Re: What is the syntax for isnull in the IIF function in the query statement

Hi all,

What is the syntax for isnull in the IIF function in the query statement for SQL Server2K?

I'm trying to create a view to get a total in a field. If the quantity is null I want to display 0 in that field.

Thanks!!

SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, IIf(dbo.tblBag_data.bag_quantity IS NULL, 0,
dbo.tblBag_data.bag_quantity) AS qty_on_hand, dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name,
dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num
FROM dbo.tblShipping_sched LEFT OUTER JOIN
dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
dbo.tblShipping_sched.work_ord_num = dbo.tblBag_data.work_ord_num
GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_numsql server doesn't support IIF, you have to use CASE

incorrect --

IIf(dbo.tblBag_data.bag_quantity IS NULL, 0, dbo.tblBag_data.bag_quantity)

correct --

case when dbo.tblBag_data.bag_quantity IS NULL then 0 else dbo.tblBag_data.bag_quantity end