| Author |
 Topic  |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
 Posted - 01/28/2013 : 12:27:13
|
CASE WHEN CAST(YR.YEAR_LABEL AS VARCHAR) + CAST(NL.DET_PERIODNUMBR AS VARCHAR) < '201207' THEN P678 ELSE P999
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
Flowing Fount of Yak Knowledge
1744 Posts |
Posted - 01/28/2013 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/28/2013 : 13:02:22
|
Also, make sure to specify the length of the varchar
CASE WHEN CAST(YR.YEAR_LABEL AS VARCHAR(4)) + RIGHT('00'+CAST(NL.DET_PERIODNUMBR AS VARCHAR(3)),2) < '201207' THEN P678 ELSE P999 END
Jim
Everyday I learn something that somebody else already knew |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48099 Posts |
|
|
James K
Flowing Fount of Yak Knowledge
1744 Posts |
|
|
James K
Flowing Fount of Yak Knowledge
1744 Posts |
Posted - 01/28/2013 : 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
India
48099 Posts |
Posted - 01/28/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 01/28/2013 : 14:06:40
|
| Thanks all, I'll try in the morning. |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 01/29/2013 : 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
India
48099 Posts |
Posted - 01/29/2013 : 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 difference
SELECT *
FROM(
SELECT '20121' AS val UNION ALL
SELECT '201207'
)t
ORDER BY Val
SELECT *
FROM(
SELECT '20121' AS val UNION ALL
SELECT '201207'
)t
ORDER BY Val*1
output
----------------------------
val
--------------------------
201207
20121
val
------------------------------
20121
201207
in second case the * 1 forces it to make it integer before ordering which makes the difference
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 01/29/2013 : 11:44:06
|
| Excellent illustration, many thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48099 Posts |
Posted - 01/29/2013 : 11:45:07
|
welcome 
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Why doesn't this line of code work?
