Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unhappy Unanswered: 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_num

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •