How To Use IS NOT NULL in a Case Statement

Question

Post reply

How To Use IS NOT NULL in a Case Statement

Teee-SQL

SSCrazy

Points: 2218
More actions
August 2, 2013 at 12:38 am

#280744

Hi Guys,
I'd like to know a correct way of using the query below in a case statement:
and it.bitIsAsset = Case it.bitIsAsset When 1 Then it.intItemId IS NOT NULL Else 'All' End --it ia as Alias for my table
This is used in the WHERE Clause, If bitIsAsset = 1 Then the intItemId mustn't be NULL Else 'All' but my query doesn't work.
any help would be appreciated.
Thanks
Teee

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

The information is not sufficient to provide you the solution...

Can you please some more detailed information so that we can help you?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

what I understand with ur prob is that you can write CAsE statement in this manner-

SELECT

Case when 1=1 THEN 'Yes' ELSE 'No' END

WHERE 1 IS NOT NULL

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Teee-SQL SSCrazy Points: 2218 More actions · Answer 3

Select

jc.intJobCardId

,b.vchBarcodeas [Barcode]

,i.dteIncidentDateas [IncidentDate]

,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '')as [ServiceProvider]

,dteAssignedDateas [AssignedDate]

,dteCompletedDateas [CompletedDate]

,DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [TimeToComplete]

,ISNULL(lb.TotalHours, 0)as [TotalHours]

,lb.TotalHours/DATEDIFF(Hour, dteAssignedDate,dteCompletedDate)as [Efficiency]

,''as [Productivity]

,l.vchLocation

,typ.vchType

From dtlIncident i

Inner Join dtlJobCard jc on i.uidId = jc.uidIncidentId

Left Join (Select intJobCardId,

SUM(decHoursWorked + decOvertime1HoursWorked +decOvertime2HoursWorked + decOvertime3HoursWorked ) as TotalHours

From dtlLabour

Group By intJobCardId) lb on jc.intJobCardId = lb.intJobCardId

Left Join dtlParty p on jc.uidServiceProviderOrganisationId = p.uidPartyId

Left Join dtlParty p2 on jc.uidServiceProviderPersonId = p2.uidPartyId

Inner Join dtlBarcode b with (nolock) on i.uidBarcodeId = b.uidId

Left Join dtlItem it on jc.intItemId = it.intItemId

Where i.dteIncidentDate between @dteFromDate and @dteToDate

and it.bitIsAsset = Case @bitAsset When 1 Then it.intItemId IS NOT NULL Else 'All' End --This is the part that's breaking the script

I'm actually passing bitIsAsset as a parameter( When its = 1 the the itemid must not be blank or null) else the parameter value must be All

I just don't know how to use IS NOT NULL in a case statement.

Thanks

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

Teee (8/2/2013)
Select
jc.intJobCardId
,b.vchBarcodeas [Barcode]
,i.dteIncidentDateas [IncidentDate]
,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '')as [ServiceProvider]
,dteAssignedDateas [AssignedDate]
,dteCompletedDateas [CompletedDate]
,DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [TimeToComplete]
,ISNULL(lb.TotalHours, 0)as [TotalHours]
,lb.TotalHours/DATEDIFF(Hour, dteAssignedDate,dteCompletedDate)as [Efficiency]
,''as [Productivity]
,l.vchLocation
,typ.vchType
From dtlIncident i
Inner Join dtlJobCard jc on i.uidId = jc.uidIncidentId
Left Join (Select intJobCardId,
SUM(decHoursWorked + decOvertime1HoursWorked +decOvertime2HoursWorked + decOvertime3HoursWorked ) as TotalHours
From dtlLabour
Group By intJobCardId) lb on jc.intJobCardId = lb.intJobCardId
Left Join dtlParty p on jc.uidServiceProviderOrganisationId = p.uidPartyId
Left Join dtlParty p2 on jc.uidServiceProviderPersonId = p2.uidPartyId
Inner Join dtlBarcode b with (nolock) on i.uidBarcodeId = b.uidId
Left Join dtlItem it on jc.intItemId = it.intItemId
Where i.dteIncidentDate between @dteFromDate and @dteToDate
and it.bitIsAsset = Case @bitAsset When 1 Then it.intItemId IS NOT NULL Else 'All' End --This is the part that's breaking the script
I'm actually passing bitIsAsset as a parameter( When its = 1 the the itemid must not be blank or null) else the parameter value must be All
I just don't know how to use IS NOT NULL in a case statement.
Thanks

Try this now-

Select

jc.intJobCardId

,b.vchBarcode as [Barcode]

,i.dteIncidentDate as [IncidentDate]

,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '') as [ServiceProvider]

,dteAssignedDate as [AssignedDate]

,dteCompletedDate as [CompletedDate]

,DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [TimeToComplete]

,ISNULL(lb.TotalHours, 0) as [TotalHours]

,lb.TotalHours/DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [Efficiency]

,'' as [Productivity]

,l.vchLocation

,typ.vchType

From dtlIncident i

Inner Join dtlJobCard jc on i.uidId = jc.uidIncidentId

Left Join (Select intJobCardId,

SUM(decHoursWorked + decOvertime1HoursWorked +decOvertime2HoursWorked + decOvertime3HoursWorked ) as TotalHours

From dtlLabour

Group By intJobCardId) lb on jc.intJobCardId = lb.intJobCardId

Left Join dtlParty p on jc.uidServiceProviderOrganisationId = p.uidPartyId

Left Join dtlParty p2 on jc.uidServiceProviderPersonId = p2.uidPartyId

Inner Join dtlBarcode b with (nolock) on i.uidBarcodeId = b.uidId

Left Join dtlItem it on jc.intItemId = it.intItemId

Where i.dteIncidentDate between @dteFromDate and @dteToDate

and it.bitIsAsset = Case @bitAsset When 1 Then (SELECT it.intItemId FROM dtlItem it WHERE it.intItemId IS NOT NULL) Else 'All' End

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Teee-SQL SSCrazy Points: 2218 More actions · Answer 5

This works perfectly, Thank you all for your responses. 🙂

Teee-SQL SSCrazy Points: 2218 More actions · Answer 6

This works perfectly, Thank you all for your responses. 🙂

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

Teee (8/2/2013)
This works perfectly, Thank you all for your responses. 🙂

Welcome :hehe:

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

kapil_kk (8/2/2013)

...
Try this now-
Select
jc.intJobCardId
,b.vchBarcode as [Barcode]
,i.dteIncidentDate as [IncidentDate]
,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '') as [ServiceProvider]
,dteAssignedDate as [AssignedDate]
,dteCompletedDate as [CompletedDate]
,DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [TimeToComplete]
,ISNULL(lb.TotalHours, 0) as [TotalHours]
,lb.TotalHours/DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [Efficiency]
,'' as [Productivity]
,l.vchLocation
,typ.vchType
From dtlIncident i
Inner Join dtlJobCard jc on i.uidId = jc.uidIncidentId
Left Join (Select intJobCardId,
SUM(decHoursWorked + decOvertime1HoursWorked +decOvertime2HoursWorked + decOvertime3HoursWorked ) as TotalHours
From dtlLabour
Group By intJobCardId) lb on jc.intJobCardId = lb.intJobCardId
Left Join dtlParty p on jc.uidServiceProviderOrganisationId = p.uidPartyId
Left Join dtlParty p2 on jc.uidServiceProviderPersonId = p2.uidPartyId
Inner Join dtlBarcode b with (nolock) on i.uidBarcodeId = b.uidId
Left Join dtlItem it on jc.intItemId = it.intItemId
Where i.dteIncidentDate between @dteFromDate and @dteToDate
and it.bitIsAsset = Case @bitAsset When 1 Then (SELECT it.intItemId FROM dtlItem it WHERE it.intItemId IS NOT NULL) Else 'All' End

If this works, I think it's by accident and not by design. It shouldn't be necessary to read the dtlItem table again, and the new subquery could return more than one result which would generate an error. I think this is what you are looking for:

WHERE i.dteIncidentDate BETWEEN @dteFromDate AND @dteToDate

AND (

(@bitAsset = 1 AND it.bitIsAsset = it.intItemId AND it.intItemId IS NOT NULL)

OR

(@bitAsset <> 1 AND it.bitIsAsset = 'All')

)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Teee-SQL SSCrazy Points: 2218 More actions · Answer 9

Lol! it actually worked when I used IN instead of = but the data that was returned wasn't correct, the second bit works fine.

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Teee (8/2/2013)
Lol! it actually worked when I used IN instead of = but the data that was returned wasn't correct, the second bit works fine.
Thanks

That's because the subquery in Kapil's code

and it.bitIsAsset = Case @bitAsset When 1 Then (SELECT it.intItemId FROM dtlItem it WHERE it.intItemId IS NOT NULL) Else 'All' End

isn't correlated to the outer SELECT - it can return any old rows so long as intItemId IS NOT NULL.

Note that the LEFT JOIN to dtlItem will be converted to an INNER JOIN by referencing columns in the WHERE clause (unless you are checking for a NULL).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

🙁

Thanks Chris for correcting........

Teee: I apologize you for not providing the proper solution!!

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

Actually I didnt see the outer query and was just focused on correcting Case statement...

I read query and see that case can return multiple values and this can lead to incorrect result......... :ermm:

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/