SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How To Use IS NOT NULL in a Case Statement


How To Use IS NOT NULL in a Case Statement

Author
Message
Teee-SQL
Teee-SQL
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 229
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
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5394 Visits: 2767
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
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5394 Visits: 2767
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
Teee-SQL
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 229
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 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
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5394 Visits: 2767
Teee (8/2/2013)
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 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
Teee-SQL
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 229
This works perfectly, Thank you all for your responses. :-)
Teee-SQL
Teee-SQL
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 229
This works perfectly, Thank you all for your responses. :-)
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5394 Visits: 2767
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
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42190 Visits: 20012
kapil_kk (8/2/2013)
[quote][b]...
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
Exploring Recursive CTEs by Example Dwain Camps
Teee-SQL
Teee-SQL
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 229
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search