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 12»»

How To Use IS NOT NULL in a Case Statement Expand / Collapse
Author
Message
Posted Friday, August 2, 2013 12:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:52 AM
Points: 62, Visits: 222
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
Post #1480255
Posted Friday, August 2, 2013 12:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1480259
Posted Friday, August 2, 2013 12:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1480260
Posted Friday, August 2, 2013 12:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:52 AM
Points: 62, Visits: 222
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
Post #1480261
Posted Friday, August 2, 2013 12:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1480264
Posted Friday, August 2, 2013 12:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:52 AM
Points: 62, Visits: 222
This works perfectly, Thank you all for your responses.
Post #1480266
Posted Friday, August 2, 2013 12:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:52 AM
Points: 62, Visits: 222
This works perfectly, Thank you all for your responses.
Post #1480267
Posted Friday, August 2, 2013 12:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
Teee (8/2/2013)
This works perfectly, Thank you all for your responses.

Welcome



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1480268
Posted Friday, August 2, 2013 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 6,805, Visits: 14,021
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
Post #1480275
Posted Friday, August 2, 2013 1:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:52 AM
Points: 62, Visits: 222
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
Post #1480278
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse