Desktop productivity for business analysts and programmers

Question on computed columns

Reply
New Contributor
Posts: 3

Question on computed columns

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

Esteemed Advisor
Posts: 7,300

Question on computed columns

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.


New Contributor
Posts: 3

Question on computed columns

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.

Esteemed Advisor
Posts: 7,300

Question on computed columns

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.

New Contributor
Posts: 3

Question on computed columns

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

Ask a Question
Discussion stats
  • 4 replies
  • 468 views
  • 6 likes
  • 2 in conversation