If statement

[lcostello]

 I want to make an if statement where if a date is filled in one case statement executes otherwise the other one does, but I am having trouble.  What am I doing wrong?

 

 

 

Code:

SELECT     C6CLIENT."CLIENT_ID" AS C6CLIENT_CLIENT_ID,     C6CLIENT."LFULLNAME" AS C6CLIENT_LFULLNAME,     C6CLIEVT."DATE1" AS C6CLIEVT_DATE1,     C6CLIEVT."DATE2" AS C6CLIEVT_DATE2,     C6CLIEVT."DATE3" AS C6CLIEVT_DATE3,     C6CLIEVT."TYPE" AS C6CLIEVT_TYPE,     C6CLIEVT."DESCRIP" AS C6CLIEVT_DESCRIP,     C6CLIEVT."STATUS" AS C6CLIEVT_STATUS,C6CLIEVT."USER1" AS C6CLIEVT_USER1,if C6CLIEVT."DATE3" <> NULL THEN((caseWhen Days(C6CLIEVT."DATE2", C6CLIEVT."DATE3") BETWEEN 0 and 29 Then 1 else 0  END) as Under30Days,(case WHEN Days(C6CLIEVT."DATE2", C6CLIEVT."DATE3") BETWEEN 30 and 59 Then 1 else 0  END) as Thirty2Sixty,(CaseWHENDays(C6CLIEVT."DATE2", C6CLIEVT."DATE3") BETWEEN 60 and 89 Then 1 else 0  END) as Sixty2Eighty9,(CaseWHENDays(C6CLIEVT."DATE2", C6CLIEVT."DATE3") >=90 Then 1 else 0  END) as Over90)ELSEif C6CLIEVT."DATE3" IS NULL THEN((caseWhen Days(C6CLIEVT."DATE2", CURRENT DATE) BETWEEN 0 and 29 Then 1 else 0  END) as Under30Days,(case WHEN Days(C6CLIEVT."DATE2", CURRENT DATE) BETWEEN 30 and 59 Then 1 else 0  END) as Thirty2Sixty,(CaseWHENDays(C6CLIEVT."DATE2", CURRENT DATE) BETWEEN 60 and 89 Then 1 else 0  END) as Sixty2Eighty9,(CaseWHENDays(C6CLIEVT."DATE2", CURRENT DATE) >=90 Then 1 else 0  END) as Over90)END IFFROM     "DBA"."C6CLIENT" C6CLIENT inner JOIN "DBA"."C6CLIEVT" C6CLIEVT ON C6CLIENT."C4_ID" = C6CLIEVT."C4_ID"WHEREC6CLIEVT_TYPE >= $P{from_type}andC6CLIEVT_TYPE <= $P{to_type}

[cmatthews]

Don't use if in the select, this "if C6CLIEVT."DATE3" <> NULL" should be more like this "CASE WHEN C6CLIEVT."DATE3" IS NOT NULL "

 

Nested case statements should be fine.