IF statement to return 0 value based on a string

[Dave Chiskey]

Hi Team,
I would like to ask for some help on the below please, hopefully it is clear from the description and example:

I have a field in my Oracle SQL table:

• LOGIN_TIME - this is a result after calculating the ENDTIME minus STARTTIME fields - both are time stamp fields in the format '7/30/2024 5:56:18.988000 PM'

I am trying to achieve:

• if the STATUS field = 'AWAY' then the LOGIN_TIME field should = 0
  • otherwise keep the calculation ENDTIME minus STARTTIME
• I would also like to show the LOGIN_TIME field as HH:MM:SS if possible

Please can you advise? This is my code:

SELECT DISTINCT a.SESSIONID,
d.USERNAME,
d.FNAME|| ' '||d.LNAME AS FULLNAME,
d.HOMEWKGRPID_NAME,
a.STARTTIME,
a.ENDTIME,
a.STATUS,
a.ENDTIME-A.STARTTIME AS LOGIN_TIME

from USERACCESSLOGSTATUS a INNER JOIN USERTABLE d ON a.USERID=d.ID
WHERE a.RDB_INSERT_DATE >= to_timestamp('2024-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND d.HOMEWKGRPID_NAME LIKE ('REGION_%')

I have tried IF and CASE but there seems to be an error between adding the '0' and the result of LOGIN_TIME calculation - presuming the issue is between data types (numeric vs. timestamp) but not sure

 

[cwstudio2020]

You should use the "CASE" key word. The modified SQL statement is as follows:
SELECT DISTINCT a.SESSIONID,
d.USERNAME,
d.FNAME|| ' '||d.LNAME AS FULLNAME,
d.HOMEWKGRPID_NAME,
a.STARTTIME,
a.ENDTIME,
a.STATUS,
CASE WHEN status = 'AWAY' then '0'
ELSE a.ENDTIME - a.STARTTIME END AS LOGIN_TIME

from USERACCESSLOGSTATUS a INNER JOIN USERTABLE d ON a.USERID=d.ID
WHERE a.RDB_INSERT_DATE >= to_timestamp('2024-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND d.HOMEWKGRPID_NAME LIKE ('REGION_%');