Author |
Topic |
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-01-28 : 12:27:13
|
[code]CASE WHEN CAST(YR.YEAR_LABEL AS VARCHAR) + CAST(NL.DET_PERIODNUMBR AS VARCHAR) < '201207' THEN P678 ELSE P999[/code]YR.YEAR_LABEL is 2012, NL.DET_PERIODNUMBR is 01, it doesn't work, it returns P999.It DOES work if YR.YEAR_LABEL is 2011 or 2010?What's wrong with my syntax?Thanks as always. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 12:32:05
|
It's probably because the string concatenation results in 20121 rather than 201201. Can you try this:CASE WHEN CAST(YR.YEAR_LABEL AS VARCHAR) + RIGHT('00'+CAST(NL.DET_PERIODNUMBR AS VARCHAR),2) < '201207' THEN P678 ELSE P999 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-28 : 13:02:22
|
Also, make sure to specify the length of the varcharCASE WHEN CAST(YR.YEAR_LABEL AS VARCHAR(4)) + RIGHT('00'+CAST(NL.DET_PERIODNUMBR AS VARCHAR(3)),2) < '201207' THEN P678 ELSE P999 END JimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 13:07:47
|
Thank you Jim, for catching that. Here is a nice example of why what Jim said is important: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 13:08:26
|
Visakh, you and I are thinking alike! I was just a minute too slow in my thinking, but oh well!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-28 : 13:11:37
|
quote: Originally posted by James K Visakh, you and I are thinking alike! I was just a minute too slow in my thinking, but oh well!!
Great minds think alike ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-01-28 : 14:06:40
|
Thanks all, I'll try in the morning. |
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-01-29 : 04:46:08
|
Tried the code this morning and it works fine so thanks again.Just a final question re the original issue: 20121 IS less than 201201 but still failed the test - is it something to do with how SQL is "seeing" the value? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 04:53:14
|
quote: Originally posted by Rasta Pickles Tried the code this morning and it works fine so thanks again.Just a final question re the original issue: 20121 IS less than 201201 but still failed the test - is it something to do with how SQL is "seeing" the value?
Thats because SQL Server orders them as string values not as numerical values.See the differenceSELECT *FROM(SELECT '20121' AS val UNION ALLSELECT '201207')tORDER BY ValSELECT *FROM(SELECT '20121' AS val UNION ALLSELECT '201207')tORDER BY Val*1output----------------------------val--------------------------20120720121val------------------------------20121201207 in second case the * 1 forces it to make it integer before ordering which makes the difference------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-01-29 : 11:44:06
|
Excellent illustration, many thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 11:45:07
|
welcome ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|