SQL Rewrite without the NULLIF

Zack
Zack used Ask the Experts™
on
Hi EE,

The 3rd party SQL interpreter I am running this script via doesn't like the NULLIF the script works fine when ran within SQL Server directly.

select NULLIF(count(*)*100, 0) as Number from vwReg_RMFeedback_Posted where IncidentInvolved = 'Complaint' 
and (select count(*) from tblDayDates where DayDate between IncidentDate and FBAckDate and DateName(WeekDay,DayDate) not in ('Saturday', 'Sunday')) <= 3
and FBAckDate is not null and Site Like '%Bay%'

Open in new window


Is there a way to rewrite this SQL script without the NULLIF?

Any assistance is welcome.

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
based on your logic, you can try:

select count(*)*100 as Number 
from vwReg_RMFeedback_Posted 
where IncidentInvolved = 'Complaint' 
and (select count(*) from tblDayDates where DayDate between IncidentDate and FBAckDate and DateName(WeekDay,DayDate) not in ('Saturday', 'Sunday')) <= 3
and FBAckDate is not null and Site Like '%Bay%'

Open in new window

Try changing select to the following, which is effectively the same:

select case when count(*)*100 = 0 then null else count(*)*100 end  as Number 

Open in new window

ZackGeneral IT Goto Guy

Author

Commented:
Thank you that work my apologies for the delayed response.
Ryan ChongSoftware Team Lead

Commented:
technically that won't return a null for count(*) if there's record(s) returned, so you no need to compare it with case when if it met that condition.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial