SQL If not null then insert N

  • Is there a way to write a line into a SQL so that the column only shows a N if there is something in the field it is reading from?

    i.e.

    PERSON.TERMINATION_DATE,

    Only want this column to show an N if there is a termination date.

  • You can use the IIF function - IIF(Person.Termination_Date IS NOT NULL, 'N', 'WhatEverYouWantInCaseOfNullHere')

    Adi

  • SQL provides a CASE expression to test for things like that:

    SELECT ..., CASE WHEN PERSON.TERMINATION_DATE > '19000101' THEN 'N' ELSE '' END AS TERMINATION, ...

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Another approach would be the CASE statement, so something like:

    CASE WHEN Person.Termination_Date IS NOT NULL THEN 'N' ELSE 'Y' END

    I prefer CASE statements over IIF's, but you get the same net result, just a different approach.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Use the ISNULL() function.

    ISNULL(<value_to_test>, <result_if_null>)

    -- sample data
    CREATE TABLE #IsNullTestr(EmpID char(3), TermDate datetime);
    INSERT #IsNullTestr(EmpID, TermDate)
    SELECT 'AAA', NULL UNION ALL
    SELECT 'AAB', '2021-01-01' UNION ALL
    SELECT 'AAC', NULL UNION ALL
    SELECT 'AAD', '2019-01-01';

    -- If you wish to display 'N' instead of a Termination Date, note that 'N' is a character value.
    -- You will have type clashes if the target of the 'N' is a date/time field.

    -- This will fail
    SELECT EmpID, IsNull(TermDate, 'N')
    FROM #IsNullTestr
    ORDER BY EmpID;

    -- This will work
    SELECT EmpID, IsNull(convert(varchar(32), TermDate, 101), 'N')
    FROM #IsNullTestr
    ORDER BY EmpID;

    Eddie Wuerch
    MCM: SQL

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply