December 13, 2011 at 6:12 am
Daniel Pokrývka (12/13/2011)
Thank you very much for the clarification. My question was inspired by real issue and final solution let me to assume what I've posted. I am glad that MVP means something 🙂
I appreciate the effort to post the interesting question. Thanks!
And thanks to Hugo for the explanation!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
December 13, 2011 at 7:45 am
Definitely learned something today. Thanks!
December 13, 2011 at 8:42 am
Interesting question and great replies. Learned something as well today.
December 13, 2011 at 8:43 am
Interesting question - tks for the added explaination Hugo.
December 13, 2011 at 8:50 am
Interesting question which provoked a good discussion.
December 13, 2011 at 9:18 am
I suppose we could also wonder about the relevance of the title, whether there is any guarantee the join will produce any rows or not, and what is meant by 'non-integer' data. For example, in the true tradition of any QotD that mentions conversion to an INT:
DECLARE @Example TABLE
(
flag BIT NOT NULL,
data VARCHAR(30) NOT NULL
)
INSERT @Example (flag, data) VALUES (1, '-')
INSERT @Example (flag, data) VALUES (1, '+')
INSERT @Example (flag, data) VALUES (1, '3')
INSERT @Example (flag, data) VALUES (1, '')
INSERT @Example (flag, data) VALUES (1, '2')
INSERT @Example (flag, data) VALUES (1, '1')
INSERT @Example (flag, data) VALUES (0, '-')
INSERT @Example (flag, data) VALUES (0, '+')
INSERT @Example (flag, data) VALUES (0, '')
INSERT @Example (flag, data) VALUES (0, '3')
INSERT @Example (flag, data) VALUES (0, '2')
INSERT @Example (flag, data) VALUES (0, '1')
SELECT
e.flag,
e.data
FROM @Example AS e
ORDER BY
(
CASE
WHEN flag = 1
THEN CONVERT(INTEGER, data)
ELSE data
END
)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 13, 2011 at 9:29 am
.
Although Hugo's post is written with clarity and precision, I had to read it about 6 times before I understood what was going on. Not sure why I'm so dense today but I'm glad I stuck with it. Important lesson here. For that reason, great question!
December 13, 2011 at 10:04 am
As previous posters have commented, the issue is not "because in CASE statements all possibilites are being evaluated" but because the CASE expression must return a single data type so the VARCHAR value from the ELSE is implicitly converted to INT, even when c.IsInsuranceLength <> 1.
Assuming that c.IsInsuranceLength = 1 is an appropriate indicator for an integer value, the following will allow sorting integer values as integers (so for example the results are ordered '1', '2', '10' instead of '1', '10', '2') then by the non-integer value:
SELECT
cc.[Value]
FROM [COMMON].[Parameters].[tCubeColumn] cc
INNER JOIN COMMON.[Parameters].tCube c ON cc.rCube=c.Id
ORDER BY
CASE WHEN c.IsInsuranceLength <> 1 THEN cc.Value END,
CASE WHEN c.IsInsuranceLength = 1 THEN CONVERT(INT, cc.Value) END
December 13, 2011 at 11:03 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 13, 2011 at 1:17 pm
Britt Cluff (12/13/2011)
This is valid code and the CONVERT will only fail when IsInsurance = 1 and Value <> Integer value.
Have you tried that?
December 13, 2011 at 11:40 pm
Choosed third option.got it wrong. did not think about data.
in question they did not given any sample data.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
December 16, 2011 at 12:26 pm
Great question, very interesting, mistaken explanation, and interesting discussion.
Tom
December 16, 2011 at 12:35 pm
SQL Kiwi (12/13/2011)
I suppose we could also wonder about the relevance of the title, whether there is any guarantee the join will produce any rows or not, and what is meant by 'non-integer' data.
Surely it is not relevant whether the join will produce any rows? We know that the table containing the sometimes integer field has rows, and the optimiser may choose to do those conversions even in the case where the join produces no rows. Your post made me think maybe I misunderstood this so I tested it and found that my 2008 R2 installation hits the error when there is 1 row in the table we know is non-empty and no rows in the other table.
Tom
December 16, 2011 at 12:41 pm
Matt Marston (12/13/2011)
As previous posters have commented, the issue is not "because in CASE statements all possibilites are being evaluated" but because the CASE expression must return a single data type so the VARCHAR value from the ELSE is implicitly converted to INT, even when c.IsInsuranceLength <> 1.Assuming that c.IsInsuranceLength = 1 is an appropriate indicator for an integer value, the following will allow sorting integer values as integers (so for example the results are ordered '1', '2', '10' instead of '1', '10', '2') then by the non-integer value:
SELECT
cc.[Value]
FROM [COMMON].[Parameters].[tCubeColumn] cc
INNER JOIN COMMON.[Parameters].tCube c ON cc.rCube=c.Id
ORDER BY
CASE WHEN c.IsInsuranceLength <> 1 THEN cc.Value END,
CASE WHEN c.IsInsuranceLength = 1 THEN CONVERT(INT, cc.Value) END
Your order by clause is a bit obscure, it would be clearer to add "ELSE NULL" into each of the two CASE expressions.
Tom
December 16, 2011 at 12:43 pm
Revenant (12/13/2011)
Britt Cluff (12/13/2011)
This is valid code and the CONVERT will only fail when IsInsurance = 1 and Value <> Integer value.Have you tried that?
Clearly he hasn't.
Tom
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply