Select Case When 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

General SQL Server Forums

New to SQL Server Programming

Select Case When question

Author

Topic

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-03-13 : 12:14:03

Hello all.

SELECT Case When ISNUMERIC(emp_id) = 1
THEN Cast(emp_id as NUMERIC)
ELSE
Cast(null as NUMERIC)
END AS ID

Why Cast emp_id as NUMERIC when the check to see if it's = 1?
What does the line under ELSE do exactly?

Please help.
Thank you.

siumui

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-13 : 12:17:15

Without knowing the datatype of "emp_id" it's hard to tell why it would be casted to numeric. We can assume, since it is being tested to see if it is numeric (which probably isn't a good idea to use that function) that it is not a numeric datatype to begin with. So, it is being casted to a numeric (without specifiying the scale or precision, which is also bad). The part after the ELSE is saying: I cannot cast this emp_id value to a numeric so make it NULL.

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-03-13 : 12:24:13

Thank you very much Lamprey!

I have questions with joining multiple tables if you can help.

select *
from employee a
Left Join department b
On a.id = b.id
Left Join salary c
On a.id = c.id

My question is about the order of the join ......table a will be joined with table b first. Then the result of the joined from table a and b will then be join with table c, correct?

siumui

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:06:20

why is order of join important here? all you need to understand here is that join is based on column value of table a (employee) so based on relationship it tries to link to department and salary and retrives related details if it exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-03-13 : 17:20:43

quote:
Originally posted by visakh16

why is order of join important here? all you need to understand here is that join is based on column value of table a (employee) so based on relationship it tries to link to department and salary and retrives related details if it exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Actually I just want to understand how join works as in if there are 3 tables to be joined from my example, table a will be joined with table b, then the result of joined (a & b) will be joined with table c, correct? That's all I'm trying to understand really.

Thanks.

siumui

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-13 : 18:41:34

Nope, SQL will do it wichever way it deams the most effecient (cost based).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 22:52:27

quote:
Originally posted by siumui

Hello all.

SELECT Case When ISNUMERIC(emp_id) = 1
THEN Cast(emp_id as NUMERIC)
ELSE
Cast(null as NUMERIC)
END AS ID

Why Cast emp_id as NUMERIC when the check to see if it's = 1?
What does the line under ELSE do exactly?

Please help.
Thank you.

siumui

please keep in mind that ISNUMERIC check will not necessarily return only numeric data

check below


SELECT ISNUMERIC('$'),ISNUMERIC(',')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources