|
|
You're in the
sql server programming
group:PICKLIST IN SQL
sql server programming:
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
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 */ [quoted text, click to view] "Ridhima Sood" <RidhimaSood@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
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 [quoted text, click to view] "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
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 =3D 3 SELECT=20 CASE @Title WHEN 1 THEN 'Mr.' WHEN 2 THEN 'Mrs.' ELSE 'Unknown' END --=20 Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience.=20 Most experience comes from bad judgment.=20 - Anonymous [quoted text, click to view] "Ridhima Sood" <RidhimaSood@discussions.microsoft.com> wrote in message = to=20news:5E3E08F1-6EA4-4FF7-8DB1-816E101F4763@microsoft.com... > hi >=20 > I am keen to know the sql command to display picklist values rather = than=20 > unique numbers. >=20 > for example, title is 1=3Dmr and 2 =3D mrs > if i say select title from contact sql returns 1 and 2 but i want it =
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. [quoted text, click to view] "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" <RidhimaSood@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.. [quoted text, click to view] "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
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? [quoted text, click to view] "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? > >
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 -- /* Warren Brunk - MCITP - SQL 2005, MCDBA www.techintsolutions.com */ [quoted text, click to view] "Ridhima Sood" <RidhimaSood@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
[quoted text, click to view]
> 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??? [quoted text, click to view] > 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're going to have to provide more information. DDL, sample data, desired results? (See http://www.aspfaq.com/5006
[quoted text, click to view]
> 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?
hi Warren
thanks heaps for the reply. Just a bit confused on the syntax "Else 'Error' End as Points" what does this mean please.... really appreciate your reply. regards ridhima [quoted text, click to view] "Warren Brunk" wrote:
> 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 > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > www.techintsolutions.com > */ > > > "Ridhima Sood" <RidhimaSood@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 > >
thanks heaps Hugo. That was soooooooooooo helpful and clear :)
Have a great day. :) [quoted text, click to view] "Hugo Kornelis" wrote:
> On Sun, 17 Sep 2006 13:40:01 -0700, Ridhima Sood wrote: > > >hi Warren > > > >thanks heaps for the reply. Just a bit confused on the syntax "Else 'Error' > >End as Points" what does this mean please.... > > Hi Ridhima, > > That is a combination of the last part of the CASE expression, followed > by a column alias. > > The CASE expression has two forms. Warren used the "simple CASE" syntax: > > CASE input_expression > WHEN when_expression THEN result_expression > WHEN when_expression THEN result_expression > .... > ELSE else_expression > END > > SQL Server will evaluate the input_expression. It will the evaluate each > when_expression, in order, and as soon os it finds one that matches the > input_expression, the matching result_expression is returned as the > result of the CASE expression. If no match is found, the result of > evaluating the else_expression is used as the result of the CASE. > > In the code above, "ELSE 'Error' END" is the last part of Warren's > "simple CASE" expression. > > The last part ("AS Points") is simply to give the column in the result > set the name "Points". You can use this on any column in the column list > of a SELECT statement to override the default name. (The default name is > equal to the column name if only that column is specified in the SELECT > list, or blank if an expression or a constant is used). Note that the > keyword AS is optional - though I recommend anyone to always use it, > since it can help to reduce confusion. > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Sun, 17 Sep 2006 13:40:01 -0700, Ridhima Sood wrote: >hi Warren > >thanks heaps for the reply. Just a bit confused on the syntax "Else 'Error' >End as Points" what does this mean please.... Hi Ridhima, That is a combination of the last part of the CASE expression, followed by a column alias. The CASE expression has two forms. Warren used the "simple CASE" syntax: CASE input_expression WHEN when_expression THEN result_expression WHEN when_expression THEN result_expression .... ELSE else_expression END SQL Server will evaluate the input_expression. It will the evaluate each when_expression, in order, and as soon os it finds one that matches the input_expression, the matching result_expression is returned as the result of the CASE expression. If no match is found, the result of evaluating the else_expression is used as the result of the CASE. In the code above, "ELSE 'Error' END" is the last part of Warren's "simple CASE" expression. The last part ("AS Points") is simply to give the column in the result set the name "Points". You can use this on any column in the column list of a SELECT statement to override the default name. (The default name is equal to the column name if only that column is specified in the SELECT list, or blank if an expression or a constant is used). Note that the keyword AS is optional - though I recommend anyone to always use it, since it can help to reduce confusion. --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |