-2

I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm attempting the following:

CASE WHEN 'AmortDate' IS NOT NULL THEN 1 
ELSE 0
END AS "IsAmortizing?'

I've tried several different variations (<> NULL, <>'', IS NOT NULL)

CASE WHEN ln.PAYMENT_SCHEDULE_TYPE = '0' THEN ''
WHEN ln.PAYMENT_SCHEDULE_TYPE = '1' THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '0' AND ln.PAYMENT_SCHEDULE_NBR = '1' AND lpsf.psDatePmtDueFirst <>''
THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
 WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '0' AND ln.PAYMENT_SCHEDULE_NBR = '1' AND lpsf.psDatePmtDueFirst = ''
THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120)
WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '999' AND ln.PAYMENT_SCHEDULE_NBR = '1' THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE = '0' AND ln.PAYMENT_SCHEDULE_AMOUNT = '0'
THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE IN('1','2','3') AND ln.PAYMENT_SCHEDULE_AMOUNT = '0'
THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE IN('1','2','3') AND ln.PAYMENT_SCHEDULE_AMOUNT > 0
THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120)
WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE = '6' AND ln.PAYMENT_SCHEDULE_AMOUNT = '0'
THEN CONVERT(VARCHAR(10),ln.DATE_MATURITY_NEXT,120)
ELSE ''
END AS AmortDate

Based off of the results of the derived AmortDate field above, I need to create another calculated field:

CASE WHEN AmortDate IS NOT NULL THEN 1
ELSE 0
END AS 'IsAmortizing'
2
  • 2
    Single quotes mean 'AmortDate' is a string, so it's always NOT NULL. Dec 14 '19 at 14:03
  • Please include a minimal reproducible example as I suspect you've got a join and where you're expecting rows with NULL values to exist actually don't exist at all, but I won't be sure without additional context. Dec 14 '19 at 18:02
2

Remove string delimiter from column names:

CASE WHEN AmortDate IS NOT NULL 
     THEN 1 
     ELSE 0 
END AS IsAmortizing?

Otherwise you are testning whether the string AmortDate is null.

If you need to quote an identifier, "Is.." is standard, sql server uses [Is.. ] and MYSQL uses Is...

CASE WHEN AmortDate IS NOT NULL 
     THEN 1 
     ELSE 0 
END AS "IsAmortizing?"

EDIT:

Renaming (AS ...) happens last, so you need to add a nesting level if you want to reference an alias:

SELECT CASE WHEN AmortDate IS NOT NULL 
            THEN 1 
            ELSE 0 
       END AS "IsAmortizing?", ...
FROM (
    SELECT ... AS AmortDate, ...
    FROM ...
) AS T
5
  • For MYSQL it should be Backticks
    – Lennart
    Dec 13 '19 at 18:19
  • The problem is that the 'AmortDate' is a calculated field in the query and isn't coming from a table. When I remove the '' string delimiters, then it says that it doesn't recognize the field.
    – Mike Jones
    Dec 13 '19 at 18:21
  • In that case you must put in the CASE as the field is calculated Example CASE WHEN Field1 + Field2 IS NOT NULL.....You could put the deficiency of your table or your query to help you better Dec 13 '19 at 18:26
  • @Lennart Would it be easier if I were to just make a temp table out of the AmortDate and bring it in that way?
    – Mike Jones
    Dec 13 '19 at 22:11
  • Hard to tell what what is easier without knowing more details. Have you considered a view or a generated column for AmortDate?
    – Lennart
    Dec 13 '19 at 22:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.