April 11, 2016 at 1:39 pm
I just ran into this in a query. Can anyone come up with a good explanation for why anyone would do this?
SELECT
ISNULL(MyTable.MyColumn, NULL) AS MyColumn
FROM
MyTable;
I obfuscated the actual object names, of course. In the original, the column in the ISNULL function is the same as the alias.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2016 at 1:42 pm
Maybe at some point there was an actual value for the ISNULL and instead of just removing the ISNULL they changed to to NULL?
April 11, 2016 at 2:26 pm
GSquared (4/11/2016)
I just ran into this in a query. Can anyone come up with a good explanation for why anyone would do this?
SELECT
ISNULL(MyTable.MyColumn, NULL) AS MyColumn
FROM
MyTable;
I obfuscated the actual object names, of course. In the original, the column in the ISNULL function is the same as the alias.
This has to do with the ANSI_NULLS settings, if it is off then (Column Null) = NULL returns true otherwise false
😎
April 11, 2016 at 2:40 pm
Eirikur Eiriksson (4/11/2016)
GSquared (4/11/2016)
I just ran into this in a query. Can anyone come up with a good explanation for why anyone would do this?
SELECT
ISNULL(MyTable.MyColumn, NULL) AS MyColumn
FROM
MyTable;
I obfuscated the actual object names, of course. In the original, the column in the ISNULL function is the same as the alias.
This has to do with the ANSI_NULLS settings, if it is off then (Column Null) = NULL returns true otherwise false
😎
I don't see how that relates to this. The expression will be indistinguishable from the original column regardless of whether ANSI_NULLS is on or off.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 11, 2016 at 3:45 pm
Is MyColumn of type VarChar? Perhaps what they intended to do was convert NULL values to the string 'NULL'.
For example:
ISNULL(MyTable.MyColumn, 'NULL') AS MyColumn
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 11, 2016 at 4:15 pm
Eric M Russell (4/11/2016)
Is MyColumn of type VarChar? Perhaps what they intended to do was convert NULL values to the string 'NULL'.For example:
ISNULL(MyTable.MyColumn, 'NULL') AS MyColumn
No single-quotes in the query. If the column is NULL, instead of returning NULL, it returns ... NULL. Doesn't even cast it to a different data type or anything.
Made me wonder if I was missing something. That's all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2016 at 7:40 am
GSquared (4/11/2016)
Eric M Russell (4/11/2016)
Is MyColumn of type VarChar? Perhaps what they intended to do was convert NULL values to the string 'NULL'.For example:
ISNULL(MyTable.MyColumn, 'NULL') AS MyColumn
No single-quotes in the query. If the column is NULL, instead of returning NULL, it returns ... NULL. Doesn't even cast it to a different data type or anything.
Made me wonder if I was missing something. That's all.
Yeah, but my best attempt at fitting this irregularly shaped peg into a round hole is to assume that the SQL coder intended NULL to be 'NULL'. That would sort of make sense, if there were unique constraint on the column, but they expected NULL values and wanted to stub them with something. However, if that's the case, I would have chosen something like 'N/A' or ''. If the original coder isn't around for questioning, then that would be my best assumption.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 12, 2016 at 2:33 pm
GSquared (4/11/2016)
I just ran into this in a query. Can anyone come up with a good explanation for why anyone would do this?
I can, but it will probnably involve the use of copious amounts of illegal substances by whoever wrote that $%!@
April 12, 2016 at 4:21 pm
I just found another query by the same person, where it divides a Numeric column by 100, then casts it as Varchar, then LTrims and RTrims the result, in a Select Into that goes into a temp table. From there, it casts the string explicitly to Decimal when it does Select Into from that temp table into another temp table. All of that is to turn a Numeric "cents" value into a Decimal "dollars" value.
So I think I have the answer to my original question now.
That query also hard-codes a column as '05', then, two statements later does "RIGHT(Column, 1)" on it. There are no other operations done on that column.
I think I have my work cut out for me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 13, 2016 at 1:59 am
GSquared (4/12/2016)
I just found another query by the same person, where it divides a Numeric column by 100, then casts it as Varchar, then LTrims and RTrims the result, in a Select Into that goes into a temp table. From there, it casts the string explicitly to Decimal when it does Select Into from that temp table into another temp table. All of that is to turn a Numeric "cents" value into a Decimal "dollars" value.So I think I have the answer to my original question now.
That query also hard-codes a column as '05', then, two statements later does "RIGHT(Column, 1)" on it. There are no other operations done on that column.
I think I have my work cut out for me.
Wow.
Once done, you might be ready for some of those substances yourself... 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply