Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Output of Query Expand / Collapse
Author
Message
Posted Monday, October 10, 2011 8:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 17,814, Visits: 15,741
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
Post #1187943
Posted Monday, October 10, 2011 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 2, 2014 6:57 PM
Points: 2,317, Visits: 1,362
Good question, luckily read the question twice before answering.
Post #1187944
Posted Monday, October 10, 2011 8:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:25 AM
Points: 1,144, Visits: 546
Bugger! I hate it when I know the right answer but click on the wrong choice!


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Post #1187962
Posted Monday, October 10, 2011 9:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 10:48 AM
Points: 4,126, Visits: 3,427
Simple but neat -- thank you!
Post #1187973
Posted Monday, October 10, 2011 10:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:29 AM
Points: 3,354, Visits: 2,001
Nice question about something that comes up regularly for me. Especially in writing queries for reporting.
Post #1188003
Posted Monday, October 10, 2011 10:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:36 AM
Points: 1,193, Visits: 698
Great question - unfortunately I got it wrong.

I thought it was too simple so I over compensated and assumed that the code would have "set ansi_nulls off" for the session since it is effectively saying "where col1 = null" rather than "where col1 is null".
Post #1188020
Posted Monday, October 10, 2011 10:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
good question - tks
Post #1188023
Posted Monday, October 10, 2011 11:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:24 PM
Points: 367, Visits: 1,077
I'd like to suggest a modification of the answer. What is happening is that the "CASE COLUMN WHEN" syntax does an equity comparision and then uses the result of that comparision, which of course fails with NULL, while the "CASE WHEN x" syntax uses the result of x. Which is why the given query returns null.

So, something like:

The CASE's statement has two alternative syntaxs, "CASE ColumnName WHEN Value" does an equity comparision between the column and the given value. This fails with NULLS unless ANSI_NULLS is off. The other syntax is "CASE WHEN EXPRESSION", which evalutes an expression (which may use any available columns) and uses the result of that expression to determine whether the condition has been met and the associated THEN used.
Post #1188034
Posted Monday, October 10, 2011 2:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 5,401, Visits: 7,513
Thanks for the question. It was a good caffination check this morning.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1188118
Posted Monday, October 10, 2011 2:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 13, 2014 12:36 PM
Points: 2,010, Visits: 10,967
Excellent question. Thanks!

Rob Schripsema
Accelitec, Inc
Post #1188132
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse