April 15, 2016 at 6:49 am
Hugo Kornelis (4/14/2016)
Phil Parkin (4/14/2016)
Why are you continuing with the implicit conversion from int to string?Actually, int has a higher precedence than string, so the implicit conversion should be from string to int. And probably handled when parsing/optimizing the query, not in execution.
The code is sloppy and probably not performing optimal, but I don't think incorrect results will occur.
Ok so I hit my books last night and I am going to use row_number I think for the case part, although I am looking at reworking the query as a whole.
Hugo, can I ask what was sloppy about my code? I love to learn so any pointers is great. I am looking at it with a fresh head today and can see some issues but value any input.
April 15, 2016 at 7:04 am
Kazmerelda (4/15/2016)
Hugo, can I ask what was sloppy about my code?
Not Hugo, but...
Column names with spaces in. Ordering by an ordinal position instead of the column name. Formatting (there isn't any). Lack of consistency in capitalisation. Implicit conversions (in the CASE statement you were comparing a SUM (which is an integer) to a string, forcing SQL to convert one of them before it can compare.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2016 at 7:19 am
GilaMonster, thanks for that. I do clean up my code before it goes live so it's all the same format.
Interesting re column names with spaces, I will do some research on that.
For the case statement that is my bad, years of when I was shown by someone YEARS ago how to do them they told me to do that except I know better now. I got sloppy there.
That is the first time I have ordered by an ordinal position, normally I do use column names just someone spoke about doing that and I gave it a go (after researching).
I genuinely appreciate the feedback though, how else will I get better :).
April 15, 2016 at 7:29 am
Ok so I hit my books last night and I am going to use row_number I think for the case part, although I am looking at reworking the query as a whole.
Keep in mind ROW_NUMBER will still have the same issue if you are trying to do string compares to those final string values in the case statement that you were having in the order by.
Using a case statement to change the string to the priority you want them sorted in works whether you do it in a ROW_NUMBER or an ORDER BY.
April 15, 2016 at 8:15 am
Well my query even with the CASE in there is now running in 3 seconds versus 25 so thank you so much for your pointers there.
I will battle away with this one and do a lot of reading up on CASE. For now as they want it as a one off (but eventually automated) I can do what needs to be done in Excel with the ordering.
Thanks again for the help.
April 15, 2016 at 9:16 am
Kazmerelda (4/15/2016)
Well my query even with the CASE in there is now running in 3 seconds versus 25 so thank you so much for your pointers there.I will battle away with this one and do a lot of reading up on CASE. For now as they want it as a one off (but eventually automated) I can do what needs to be done in Excel with the ordering.
Thanks again for the help.
Regarding ordering ... how are you doing it? I mean the overall ordering, which looks like it should be date driven, but isn't.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 15, 2016 at 9:57 am
Kazmerelda (4/15/2016)
Hugo, can I ask what was sloppy about my code?
Heh! Gail (Gilamonster) pointed out way more things than i would have. Truth is, I never inspected the entire query, I just focused on the parts you had a question about, and in this case specifically on the type conversions.
Since the No_Of_Applicants column is of data type integer and the constant '10' (with quotes) is string, CASE WHEN No_Of_Applicants >= '10' forces a data type conversion. Phil was afraid that the integer column would be converted to string (in which case the results would be incorrect, because the string value '2' sorts after the string value '10'). I pointed out that the implicit conversion is the other way around, so the '10' gets converted to the integer 10 and the comparison does what you expect.
The non-sloppy way of doing this would be to just remove the quotes: CASE WHEN No_Of_Applicants >= 10. Now you compare an integer column with an integer constant and no conversion whatsoever is needed.
(That being said, please do take heed of all the other things Gail wrote. She is a very smart person, so when she posts suggestions, you better take them seriously! 😉 )
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply