CASE question ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE question !

Author

Topic

dubovska
Starting Member

2 Posts

Posted - 2009-02-12 : 20:26:01

Hi everyone.
I'd like to refer to the results of the CASE in a WHERE clause. Here is my query:

Select (CASE len(symbol)
WHEN 1 THEN (symbol + ' ' + ' ' + ticker)
WHEN 2 THEN (symbol + ' ' + ticker)
ELSE (symbol + ticker)
END) as tsymbol, refdate, clearingmember, sum(pos) as TotalPos from position
where refdate = '11/17/2008' and tsymbol = 'XXXXX' group by symbol, ticker, refdate, clearingmember;

Of course, it complains about usage of the tsymbol above.
What can I do her ??? Thanks for your help !

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 20:30:13

[code]Select tsymbol, refdate, clearingmember,TotalPos
from (Select (CASE len(symbol)
WHEN 1 THEN (symbol + ' ' + ' ' + ticker)
WHEN 2 THEN (symbol + ' ' + ticker)
ELSE (symbol + ticker)
END) as tsymbol, refdate, clearingmember, sum(pos) as TotalPos from position
where refdate = '11/17/2008'
group by (CASE len(symbol)
WHEN 1 THEN (symbol + ' ' + ' ' + ticker)
WHEN 2 THEN (symbol + ' ' + ticker)
ELSE (symbol + ticker)
END),refdate, clearingmember
)Z
Where Z.tsymbol = 'XXXX'[/code]

dubovska
Starting Member

2 Posts

Posted - 2009-02-12 : 20:38:24

quote:

Originally posted by sodeep

Select tsymbol, refdate, clearingmember,TotalPos
    from (Select  (CASE len(symbol) 
                   WHEN 1 THEN (symbol + ' ' + ' ' + ticker)
                   WHEN 2 THEN (symbol + ' '  + ticker)
                   ELSE (symbol  + ticker)
                   END) as tsymbol, refdate, clearingmember, sum(pos) as TotalPos from position 
where refdate = '11/17/2008' 
group by  (CASE len(symbol) 
                   WHEN 1 THEN (symbol + ' ' + ' ' + ticker)
                   WHEN 2 THEN (symbol + ' '  + ticker)
                   ELSE (symbol  + ticker)
                   END),refdate, clearingmember
)Z
Where Z.tsymbol = 'XXXX'

THANKS ! I'LL TRY IT !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 20:45:14

quote:

Originally posted by sodeep

Select tsymbol, refdate, clearingmember,TotalPos
    from (Select  (CASE len(symbol) 
                   WHEN 1 THEN (symbol + ' ' + ' ' + ticker)
                   WHEN 2 THEN (symbol + ' '  + ticker)
                   ELSE (symbol  + ticker)
                   END) as tsymbol, refdate, clearingmember, sum(pos) as TotalPos from position 
where refdate = '11/17/2008' 
group by  (CASE len(symbol) 
                   WHEN 1 THEN (symbol + ' ' + ' ' + ticker)
                   WHEN 2 THEN (symbol + ' '  + ticker)
                   ELSE (symbol  + ticker)
                   END),refdate, clearingmember
)Z
Where Z.tsymbol = 'XXXX'

why you included cae when in group? symbol is not a derived column. you can directly use it

...........
group by symbol, ticker, refdate, clearingmember;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources