|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
PICKLIST IN SQLhi
I am keen to know the sql command to display picklist values rather than unique numbers. for example, title is 1=mr and 2 = mrs if i say select title from contact sql returns 1 and 2 but i want it to return mr and mrs..any advice on this please > for example, title is 1=mr and 2 = mrs I don't understand, does your table have 1 column, 2 columns, 3 columns, 4?If you only have 1 and 2 in your table, is SQL Server supposed to know that 1 = mr and 2 = mrs??? > if i say select title from contact sql returns 1 and 2 but i want it to You're going to have to provide more information. DDL, sample data, desired > return mr and mrs..any advice on this please results? (See http://www.aspfaq.com/5006 You could you a case statement...
select CASE When title = '1' then 'Mr.' When title = '2' then 'Mrs.' Else 'Error' End From Contact Show quote "Ridhima Sood" <RidhimaS***@discussions.microsoft.com> wrote in message news:5E3E08F1-6EA4-4FF7-8DB1-816E101F4763@microsoft.com... > hi > > I am keen to know the sql command to display picklist values rather than > unique numbers. > > for example, title is 1=mr and 2 = mrs > if i say select title from contact sql returns 1 and 2 but i want it to > return mr and mrs..any advice on this please hi Aaron
This is not a table. It is a field in CRM of type picklist.. i am not sure if i could clarify this. please let me know. Show quote "Warren Brunk" wrote: > You could you a case statement... > > > select CASE > When title = '1' then 'Mr.' > When title = '2' then 'Mrs.' > Else 'Error' > End > From Contact > > > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > www.techintsolutions.com > */ > > > "Ridhima Sood" <RidhimaS***@discussions.microsoft.com> wrote in message > news:5E3E08F1-6EA4-4FF7-8DB1-816E101F4763@microsoft.com... > > hi > > > > I am keen to know the sql command to display picklist values rather than > > unique numbers. > > > > for example, title is 1=mr and 2 = mrs > > if i say select title from contact sql returns 1 and 2 but i want it to > > return mr and mrs..any advice on this please > > > > This is not a table. It is a field in CRM of type picklist. And how are you getting to a picklist field in CRM from T-SQL?Aaron we are using an SBS CRM the database of which ties to SQL2005 and i am
using SQL Management studio 2005. Does this help please? Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > This is not a table. It is a field in CRM of type picklist. > > And how are you getting to a picklist field in CRM from T-SQL? > > > the title field is a picklist field in CRM 3.0's entity contact that i want
to get the values in for in sql Show quote "Ridhima Sood" wrote: > hi > > I am keen to know the sql command to display picklist values rather than > unique numbers. > > for example, title is 1=mr and 2 = mrs > if i say select title from contact sql returns 1 and 2 but i want it to > return mr and mrs..any advice on this please Typically, what you are referring to is done with the client application. It is so easy to do conversion and enum list using client code.
However, if you have to do something like this in SQL code, use CASE statements. However, it is NOT dynamic and does not lead itself to easy change. DECLARE @Title int SET @Title = 3 SELECT CASE @Title WHEN 1 THEN 'Mr.' WHEN 2 THEN 'Mrs.' ELSE 'Unknown' END -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Ridhima Sood" <RidhimaS***@discussions.microsoft.com> wrote in message news:5E3E08F1-6EA4-4FF7-8DB1-816E101F4763@microsoft.com... > hi > > I am keen to know the sql command to display picklist values rather than > unique numbers. > > for example, title is 1=mr and 2 = mrs > if i say select title from contact sql returns 1 and 2 but i want it to > return mr and mrs..any advice on this please Waren i am kinda lost
below is the sql statement SELECT ContactExtensionBase.New_ReferralPoints1, ContactBase.FirstName, ContactBase.LastName, ContactExtensionBase.New_ReferralMethod1, ContactExtensionBase.New_ReferralMethod2., ContactExtensionBase.New_ReferralPoints2 FROM ContactBase INNER JOIN ContactExtensionBase ON ContactBase.ContactId = ContactExtensionBase.ContactId the result is for example method first name last name points 2 Barbara Johnston 1 However i know that 1= web , 2=phone etc and for points 1=10,2=20 etc...but i cant get SQl to display the field value... i hope u get what i mean now.. Show quote "Ridhima Sood" wrote: > hi > > I am keen to know the sql command to display picklist values rather than > unique numbers. > > for example, title is 1=mr and 2 = mrs > if i say select title from contact sql returns 1 and 2 but i want it to > return mr and mrs..any advice on this please You can use a case statement to replace values on any cloumn in your select
statement. You can also use a case on more than one column. Lets Pretend that ContactExtensionBase.New_ReferralPoints1is the column points in your example. SELECT Case ContactExtensionBase.New_ReferralPoints1 When 1 then 'Web' When 2 then 'Phone' Else 'Error' End as Points, ContactBase.FirstName, ContactBase.LastName, ContactExtensionBase.New_ReferralMethod1, ContactExtensionBase.New_ReferralMethod2., ContactExtensionBase.New_ReferralPoints2 FROM ContactBase INNER JOIN ContactExtensionBase ON ContactBase.ContactId = ContactExtensionBase.ContactId Show quote "Ridhima Sood" <RidhimaS***@discussions.microsoft.com> wrote in message news:D8A01F4C-931B-453E-9806-F3486166981B@microsoft.com... > Waren i am kinda lost > below is the sql statement > SELECT ContactExtensionBase.New_ReferralPoints1, > ContactBase.FirstName, > ContactBase.LastName, ContactExtensionBase.New_ReferralMethod1, > ContactExtensionBase.New_ReferralMethod2., > ContactExtensionBase.New_ReferralPoints2 > FROM ContactBase INNER JOIN > ContactExtensionBase ON ContactBase.ContactId = > ContactExtensionBase.ContactId > > the result is for example > method first name last name points > 2 Barbara Johnston 1 > However i know that 1= web , 2=phone etc and for points > 1=10,2=20 etc...but i cant get SQl to display the field value... > > i hope u get what i mean now.. > > "Ridhima Sood" wrote: > >> hi >> >> I am keen to know the sql command to display picklist values rather than >> unique numbers. >> >> for example, title is 1=mr and 2 = mrs >> if i say select title from contact sql returns 1 and 2 but i want it to >> return mr and mrs..any advice on this please  
Other interesting topics
|
|||||||||||||||||||||||
