Link to home
Start Free TrialLog in
Avatar of earwig75
earwig75

asked on

How can I use one field instead of another in an SQL query when conditions apply

In the query below, if personOne is null, I want to output personTwo as "personOne". Can someone assist? I tried using this but it does't work

SELECT 
   id, 
   timeIn, 
   CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END AS TimeOut, -- or  ISNULL(timeOut, GETDATE()) AS timeOut, 
   carType,
   CASE WHEN personOne IS NULL THEN personTwo ELSE personOne END AS personOne,   --this doesn't work.
   limitTimeToCheckIn,
   CASE WHEN name_me_1 > timeOut THEN 1 ELSE NULL END AS name_me_2
FROM (
   SELECT id, timeIn, timeOut, carType, personOne, personTwo, limitTimeToCheckIn, DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
   FROM masterCarTable 
      LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType) a

Open in new window

Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi Earwig,

The syntax certainly looks fine.  What results are you getting?

Kent
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>if personOne is null, I want to output personTwo as "personOne".
The below will return the first non-NULL value it finds, so if PersonTwo IS NULL it will return personOne.

ISNULL(personTwo, PersonOne)

... if you meant the string "PersonOne", then ...

ISNULL(personTwo, 'PersonOne')

... if you want to do this with more than two values ...

COALESCE(personTwo, personOne, 'Nobody Loves Me')
Avatar of earwig75
earwig75

ASKER

I should have mentioned, there are other conditions, not just NULL.

personOne and personTwo are both columns, not strings.

I updated the query below:

SELECT 
   id, 
   timeIn, 
   CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END AS TimeOut, -- or  ISNULL(timeOut, GETDATE()) AS timeOut, 
   carType,
   CASE WHEN personOne IS NULL and status = 'Done' THEN personTwo ELSE personOne END AS personOne,   --this doesn't work.
   limitTimeToCheckIn,
   CASE WHEN name_me_1 > timeOut THEN 1 ELSE NULL END AS name_me_2
FROM (
   SELECT id, timeIn, timeOut, carType, status, personOne, personTwo, limitTimeToCheckIn, DATEADD(mi, limitTimeToCheckIn, timeIn) AS name_me_1
   FROM masterCarTable 
      LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType) a

Open in new window

So, with your last update, give us a situation that the expected result is not the expected one.
When I try the query with my last update, personOne is null, status is "done" and personTwo is a value, but instead of outputting personOne with the value in personTwo, it stays null.

Thank you.
Maybe personTwo is also NULL it that case?
Try this..

SELECT 
    id
   ,timeIn
   ,CASE WHEN timeOut IS NULL THEN GETDATE() ELSE timeOut END TimeOut
   ,carType
   ,CASE WHEN personOne IS NULL THEN personTwo ELSE personOne END personOne
   ,limitTimeToCheckIn
   ,CASE WHEN DATEADD(mi, limitTimeToCheckIn, timeIn) > timeOut THEN 1 ELSE NULL END name_me_2
FROM masterCarTable 
LEFT JOIN carTypesTable ON masterCarTable.carType = carTypesTable.carType

Open in new window


Hope it helps !!
You can have multiple conditions . . . this is just a sample but see below.  Please define what "doesn't means" or what your expected results should be as requested by others.

 CASE WHEN personOne IS NULL THEN personTwo 
            WHEN personTwo IS NULL THEN 'Unknown'
              ELSE personOne END AS personOne, 

Open in new window

Are you sure that PersonOne is NULL?  Perhaps it's blank.  That would require a different test.
Re:

WHEN personOne IS NULL THEN personTwo ELSE personOne END AS personOne

Your end overrides your assignment to personTwo
I apologize, I noticed I had the same column listed twice in my SQL. My original question was answered correctly so I will credit that answer. Thanks again.