SQL If not null then insert N

Question

Post reply

SQL If not null then insert N

FrancisH

SSC Enthusiast

Points: 172
More actions
June 7, 2021 at 4:24 am

#3892755

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.

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

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

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

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

Adi

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

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.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

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