How to write a Case when statement in a select sta

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 2008 Forums

Transact-SQL (2008)

How to write a Case when statement in a select sta

Author

Topic

poojamalik
Starting Member

5 Posts

Posted - 2011-05-25 : 14:00:11

How to write a Case when statement in a select statement?

I want to check if the empl_no of tbl1 is present in parent_no of tbl2, then show it has parent and if it is in child_no of tbl2 then show it as child. Also, if a parent has many children, and these children exist as a parent in tbl2, then look for corresponding children as well in tbl2

select
CASE WHEN a.empl_no = b.Parent_no THEN b.Parent_Nbr
CASE WHEN a.empl_no = b.child_no THEN b.Child_Nbr end,
a.or_val, a.or_adj, a.or_obj, a.val,
from tbl1 a left outer join tbl2 b
where a.mth >='2011-01'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 14:05:51

[code]CASE
WHEN a.empl_no = b.Parent_no THEN b.Parent_Nbr
WHEN a.empl_no = b.child_no THEN b.Child_Nbr
END,
[/code]
Two other observations:

1. You have an extra comma just before "FROM".

2. a.mth > '2011-01'. Depending on the data type of a.mth column, this may work correctly, or may not.

poojamalik
Starting Member

5 Posts

Posted - 2011-05-25 : 14:09:33

Agreed!
The error that I am facing up is :
Incorrect syntax near the keyword 'CASE'.

poojamalik
Starting Member

5 Posts

Posted - 2011-05-25 : 14:11:42

Error2 :
Invalid column name 'Parent_Nbr'.
Invalid column name 'Child_Nbr'

I guess its not recognizing the alias names(a and b) for tables

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 14:17:49

I see that you are using "b.Parent_no" and "b.Parent_Nbr". Are those both columns in tbl2? Doesn't seem like they would be. Use the actual column name whichever it is - probably b.Parent_no


CASE 
   WHEN a.empl_no = b.Parent_no THEN b.Parent_no 
   WHEN a.empl_no = b.child_no THEN b.child_no 
END,

poojamalik
Starting Member

5 Posts

Posted - 2011-05-25 : 14:40:48

Oh yes, you are right!!Thanks!

Now when I correct the query like:
select parent = CASE
WHEN a.empl_no = b.Parent_no THEN b.Parent_no end,
child = CASE
WHEN a.empl_no = b.child_no THEN b.Child_no else parent_no end, a.empl_no,b.parent_no,b.child_no
from tbl1 a left outer join tbl2 b
where a.mth >='2011-01'

I get the following output:
parent child empl_no parent_no child_no
6018427 NULL 6018427 6018427 6027176
NULL 6011758 6011758 6010640 6011758
124384 NULL 124384 124384 6041281
6012541 NULL 6012541 6012541 6011457

I was actually looking for: if the empl_no matches the parent_no, then it fills the new column parent and copies exactly whatever was there in its corresponding child_no into new column child as well.
Similarly, if empl_no matches the child_no, then it fills the new column child and copies the corresponding value of parent_no into the new column parent as well.Thus, there should not be any NULL values as seen in the output above.

poojamalik
Starting Member

5 Posts

Posted - 2011-05-25 : 15:26:12

Did I make myself clear in the query above?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 15:49:04

For the example that you gave, what should the output look like?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources