null or blank check for a date column in Table transformer code

Nags Subramanian
Contributor
May 7, 2024

Hi , i have a table transformer code written like below.  The highlighted code check if the date is blanks does not work.  could you please let me know how exactly to check for this please? It always executes the else statement and returns T1.'End date'

5th column in the below table is T1.'End date'.

Last column is the 'Story max date' that i am trying to return.

thanks

Nagarajan.

 

case when T1.'End date' ="" then "Nov 30, 2025"


when (((select max(T2.'End date') from T2 where T2.'Epic Link'=T1.'Epic Name' and T2.'End date'!="" and upper(T2.'Status') in ("NEW","IN ANALYSIS","IN PROGRESS","IN REVIEW","READY","BLOCKED") group by T2.'Epic Link' ) !=NULL)
and ((select max(T2.'End date') from T2 where T2.'Epic Link'=T1.'Epic Name' and T2.'End date'!="" and upper(T2.'Status') in ("NEW","IN ANALYSIS","IN PROGRESS","IN REVIEW","READY","BLOCKED") group by T2.'Epic Link' ) !="") )
then (select max(T2.'End date') from T2 where T2.'Epic Link'=T1.'Epic Name' and T2.'End date'!="" and upper(T2.'Status') in ("NEW","IN ANALYSIS","IN PROGRESS","IN REVIEW","READY","BLOCKED") group by T2.'Epic Link' )
else T1.'End date'
end as 'Story max date'

 

SNoDivisionEpicEpic nameEpic end dateEpic statusStory max date
94X-DivisionalX-Divisional P&C & SB Data Analytics ICTO-24278 Financial Messaging and Sanction Filtering Data MartICTO-24278Nov 30, 2025IN PROGRESSNov 30, 2025
46NCLNCL Securities Settlements (APAC) ICTO-12571 POETS-CLEARANCEICTO-12571Dec 31, 2025IN ANALYSISDec 31, 2025
15GWMGWM Bahamas ICTO-28181 Treasury SuiteICTO-28181 TO DO 
84X-DivisionalX-Divisional GCS ICTO-391 ELAR - ELARBAS CHICTO-391 TO DO 
85X-DivisionalX-Divisional GCS ICTO-4269 Regulatory Compliant Records Repository - RCR AmericasICTO-4269 TO DO

1 answer

1 accepted

3 votes
Answer accepted
Shawn Doyle - ReleaseTEAM
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 7, 2024

Instead of using != use IS NOT NULL.  You are also switching between "" and NULL, should be able to just use NULL.

From:

https://www.w3schools.com/sql/sql_null_values.asp

https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html#GeneralsyntaxandSQLfunctionsavailable-GeneralSQLsyntax

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

Nags Subramanian
Contributor
May 10, 2024

Thank you. it worked.

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events