Null values with CASE - 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 2000 Forums

Transact-SQL (2000)

Null values with CASE

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-10 : 13:21:45

Hi,
I have a query that uses case statement as...
select distinct A, b, c, d, e,f, g,h,
CASE When (ID in (100,200)) then SomeCol1 end as Princ,
CASE When (ID in (100,200)) then SomeCol2 end as PrincL,
CASE When (ID in (100,200)) then SomeCol3 end as PrincE
Since it is not using the ELSE part in CASE, it is also returning one row with NULL values for the rows satisfying the --- ID in (100,200) condition.

Any work arounds?

TIA

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-10 : 13:50:27

Use the ELSE?

Brett

8-)

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-10 : 14:00:59

I do not want records that do not satisfy the condition. I have to give some 'else' value and it will still bring back that extra record.

select distinct A, b, c, d, e,f, g,h,
CASE When (ID in (100,200)) then SomeCol1 else 'val1' end as Princ,
CASE When (ID in (100,200)) then SomeCol2 else 'val1' end as PrincL,
CASE When (ID in (100,200)) then SomeCol3 else 'val1' end as PrincE

?

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-10 : 14:03:34

So you only want rows the are id 100 or 200?

WHERE ID IN (100,200)?

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-10 : 14:30:34

So, why don't you have a

where
ID IN (100,200)

on the end of the select? That's the only way to get rid of the NULL lines. Maybe I'm just crazy and totally missing the point here though, but it seems Brett is seeing what I'm seeing.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-10 : 14:34:05

If you only want values where the ID is 100 or 200 and use this in your where clause, then you dont even need the case statement since all records would satisfy your condition, you only need to select them.

Chris

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-10 : 14:53:57

Sorry guys, I confused all.

I do want all the records and to the records that satisfy the condition ID in (100,200) I want the additional columns (somecol1, somecol2, somecol3) also listed. For the rest I do not even want those columns.
For example

a ..... somecol1 somecol2 somecol3
---------------------------------------
1 NULL NULL NULL
2 ab ad NULL
2 NULL NULL NULL
2 rc lAUDIA NULL

Row1: Does not satisfy the condition, hence the NULLS which is fine.
Row3: This is the one I am trying to eliminate.

I hope I am making sense.

Thanks for reading and helping. I really need that.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-10 : 15:02:56

This might be easier if you post the actual sql....

Also, you can't 3 columns for this row and 5 columns for the next

Do you just want to get rid of NULL? Can you work with an empty string?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 15:03:23

you have a presentation issue. you need some logic where you are presenting this data to hide or show columns or headers or whatever you wish. SQL, ODBC, DAO, OLEDB, etc all return "Square" sets of data, where all rows have an equal number of columns. IN your presentation layer (a report, an excel file, a web page) is where you can do some formatting to indent or hide columns or whatever you wish.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources