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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-03 : 08:43:00
|
| Matthew writes "I have a table (tblBlah) with say 3 rows in it...the field in tblBlah labeled nKey is 1, 2, 3 for each row. There is another field in the row title Receipts. Receipts has the value of 7 for each of the 3 records. I give this SQL to the table..Select receipts from tblBlah where nKey In('1','2','3','4')I get back 3 recs all with Receipt = 7. I want to get back the value of 0 where nKey was '4' since there wasn't a row for 4, I need a 0 back. i.e. 7,7,7,0Also, If I add the 4 at the beginning of the IN(), I want to get it back in that order.i.e. Select receipts from tblBlah where nKey In('4','1','2','3')So my result set would be 0,7,7,7++++++++++++++++++++can you help?? Thanks!!" |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-06-03 : 09:00:53
|
| Select coalesce(receipts,0) from tblBlah t right outer join (select '1' as dKey union select '2' union select '3' union select '4') as son t.nKey = s.dKey order by case s.dKey when '4' then 0when '1' then 1when '2' then 2when '3' then 3 end You have to build the statement dynamically. |
 |
|
|
|
|
|
|
|
return 0's for an IN('') when the value in the IN('') isn't there..please help!