Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: append query with isnull function

    i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.
    Thank you,

    insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms)
    SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#,
    iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) ,
    FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN
    dbo.tblCustomersIOA LEFT OUTER JOIN
    WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);

  2. #2
    Join Date
    Oct 2003
    Use this:

    ISNULL(dbo.tblCustomersIOA.Contact, '')

    ISNULL() returns the second parameter if the first param is null....
    the second has to be the same type as the first (but you can play around with CAST() and CONVERT() if needed.

    ISNULL()'s close cousin is NULLIF(Param1, Param2) which returns NULL
    if the two params are equal...Look up BOL for more information on both functions

Posting Permissions

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