Re: [SQLDownUnder] CASE STATEMENT

[grantq]

Anthony

The value returned by any tuple (a CASE result) in this instance must
be a singleton value or null.
Simply put - a select statement can appear anywhere a column or table
can appear, but when it is in the projection list it must return only
one value or null per row in the result set.  The select may be
corelated to any value in scope or none at all.  Victor's example is
assured of one value by using ungrouped aggregates. Be sure that all
values returned from the CASE are of the same datatype in the result
set. At least one DB vendor allows multi-column projections in
subqueries - which is a real treat, but so far MS SQL is not among
them.

To add further to Victor's comment about = vs AS and for
completeness... you can save keystrokes and avoid both and it is ANSI
compliant - but it decreases readability - many resist this style for
that reason - but I personally don't mind it.  e.g SELECT address
postcode, city state from table1 -assuming that all four are columns
on table1 would result in address values being aliased to postcode and
city to state - syntactically correct but probably not the intent.

hth
Grant Queenin

On Thu, 10 Mar 2005 13:52:07 +1100, Victor.Isakov@xxxxxxxxxxxxxxxxxxxx
<Victor.Isakov@xxxxxxxxxxxxxxxxxxxx> wrote:
> Something like:
>  
> CREATE TABLE #T1 (C1 INT)
> INSERT #T1 VALUES (1)
> INSERT #T1 VALUES (2)
> INSERT #T1 VALUES (3)
> INSERT #T1 VALUES (4)
> INSERT #T1 VALUES (5)
>  
> CREATE TABLE #T2 (C2 INT)
> INSERT #T2 VALUES (10)
> INSERT #T2 VALUES (20)
>  
> SELECT CASE C1
> WHEN 1 THEN (SELECT MAX(C2) FROM #T2 )
> WHEN 2 THEN (SELECT MIN(C2) FROM #T2)
> WHEN 3 THEN NULL
> ELSE C1
> END AS WEIRD
> FROM #T1
> ________________________________
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of anthony
> Sent: Thursday,10 March 2005 1:43 PM
> 
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: RE: [SQLDownUnder] CASE STATEMENT
> 
> 
> thanks...so i cannot put a SQL statement under each when?
> ________________________________
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of
> Victor.Isakov@xxxxxxxxxxxxxxxxxxxx
> Sent: Thursday, 10 March 2005 1:40 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: RE: [SQLDownUnder] CASE STATEMENT
> 
> 
> To be a little picky, sorry Peter, I would recommend avoiding = for Column
> Headings as it ain't ANSI, so:
>  
> SELECT 
> CASE 
> WHEN BankBalance > 0 THEN 'Rich' 
> WHEN BankBalance = 0 THEN 'Neutral 
> ELSE 'Broke' 
> END AS MyFinancialPosition
> ......
> ________________________________
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Peter Busch,
> Altosoft
> Sent: Thursday,10 March 2005 1:36 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: Re: [SQLDownUnder] CASE STATEMENT
> 
> 
>  
>  
> SELECT MyFinancialPosition = 
> CASE 
> WHEN BankBalance > 0 THEN 'Rich' 
> WHEN BankBalance = 0 THEN 'Neutral 
> ELSE 'Broke' 
> END
> ......
>  
> or 
>  
> SELECT MyFinancialPosition = 
> CASE BankBalance 
> WHEN > 0 THEN 'Rich' 
> WHEN = 0 THEN 'Neutral 
> ELSE 'Broke' END
> .....
>  
>  
>  
>  
>  
>  
> ----- Original Message ----- 
> From: anthony 
> To: sqldownunder@xxxxxxxxxxxxxxxxxxxxxx 
> Sent: Thursday, March 10, 2005 12:28 PM
> Subject: [SQLDownUnder] CASE STATEMENT
> 
> How do i create a CASE statement in SQL with the same functionaility as this
>  
> select case Type
> case 1
>         ....some code
> case 2
>     .......some code
> end select
>  
> AnthonySto unsubscribe to this list, please send a message back to the list
> with 'unsubscribe' as the subject. Powered by mailenable.com - List managed
> by http://www.readify.net to unsubscribe to this list, please send a message
> back to the list with 'unsubscribe' as the subject. Powered by
> mailenable.com - List managed by http://www.readify.net 
> 
> This message and any attachment is confidential and may be privileged or
> otherwise protected from disclosure. If you have received it by mistake
> please let us know by reply and then delete it from your system; you should
> not copy the message or disclose its contents to anyone.
> 
> ==============================================================================
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net to unsubscribe to this list, please send a message
> back to the list with 'unsubscribe' as the subject. Powered by
> mailenable.com - List managed by http://www.readify.net 
> 
> This message and any attachment is confidential and may be privileged or
> otherwise protected from disclosure. If you have received it by mistake
> please let us know by reply and then delete it from your system; you should
> not copy the message or disclose its contents to anyone.
> 
> ==============================================================================
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net


to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net