Question on computed columns

[DaveD]

Can anyone show me what I am doing wrong with this computed column case function?

CASE t1.event_date

WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 THEN 60

WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 AND (DATDIF(t1.event_date, DATE(), 'act/act')) LE 748 THEN 40

ELSE 0

END

It runs, but only returns 0.  When I do the DATDIF function in it's own computed column, I get the correct numerical return.

Thanks

[art297]

I would think that your first when statement would override your second statement (i.e., to meet the second statement the first statement has to be true, thus the second statement never gets to be considered.

Second, don't know if it would make a difference, but you have one redundant set of parentheses in each when statement.  the following would suffice:

WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 THEN 60

Also, you don't have to indicate t1.event_date in the case statement.

I would test the calculations by changing the select statement to:

select t1.event_date, DATDIF(t1.event_date, DATE(), 'act/act') as datedif,

CASE

WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 AND DATDIF(t1.event_date, DATE(), 'act/act') LE 748 THEN 40

WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 THEN 60

ELSE 0

END

as whatever

and see what your calculations/comparisons are really doing.

[DaveD]

Actually, less is better:

David Dillard wrote:
Can anyone show me what I am doing wrong with this computed column case function?
CASE t1.event_date
WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 THEN 60
WHEN (DATDIF(t1.event_date, DATE(), 'act/act')) GT 383 AND (DATDIF(t1.event_date, DATE(), 'act/act')) LE 748 THEN 40
ELSE 0
END
It runs, but only returns 0.  When I do the DATDIF function in it's own computed column, I get the correct numerical return.
Thanks

What works is:

CASE

WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 THEN 60

WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 AND DATDIF(t1.event_date, DATE(), 'act/act') LE 784 THEN 40

ELSE 0

END

However, I am now trying to add a prompt to the WHEN statement and can not get it to recognize the prompt.  I have associated the prompt with the query, but when I add it to the WHEN line (WHEN DATDIF(t1.event_date, DATE(), 'act/act') GT 383 + &Prompt_offset GT 383 THEN 60), it flags the &.

Any help is greatly appreciated.

[art297]

I can't answer your question about the prompt, but still advise that you change the order of your when statements.  Since the first must be correct before the second is addressed, the second one will never be recognized.

[DaveD]

You are absolutely correct.  The ordre of the statement is critical.  Thank you for pointing that out.