CASE statement using DATES

3025352

Hi all,

I need help with the following filter logic in my report.

The logic is as follows: When an Agreement Sent Date.Date is 1969 then cast it as 'NULL' or '0'.. Doesn't really matter. Else show the 'Date' field.

Here's what I currently have and it's not working.

CASE WHEN "Agreement Sent Date"."Date" = '1969-12-12' THEN CAST(NULL as TIMESTAMP) ELSE "Agreement Sent Date"."Date" END

Thank you in advance!

Pedro F

Hi,

You don't really say much that we can help you with. What isn't working? Which error are you getting?

Your CASE syntax is correct but I have some doubts that comparison between your date and '1969-12-12' won't throw an error since you aren't comparing a timestamp to a timestamp.

Anyway, unless you give more details it's hard for us to guess what isn't working.

3025352

Hi Pedro,

Thank you for your response.

This is the error I get:

Odbc driver returned an error (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <'>: Syntax error [nQSError: 26012] .Please have your System Administrator look at the log for more details on this error. (HY000)

It's not too helpful of an error message.

For some reason the system or maybe the user is entering 1969-12-12 as a date, I guess to fill the spot if there isnt one and  in the reports they want them to show up as null or 0. is that possible?

Pedro F

Looking at the error it does seem like you have an error on the date '1969-12-12'.

Where is that CASE STATEMENT being used? Is it inside a column formula? Can you please post a screenshot of that? Also post a screenshot of the values in column "Agreement Sent Date"."Date"

Andrew Fomin.

nullif("Agreement Sent Date"."Date", date '1969-12-12') ?

3025352

Here are the screenshots! I am using the CASE in a column filter

3025352

Unfortunately that's not working

Gianni Ceresa

1969 or 12-12-1969?

Because you talk about "Agreement Sent Date"."Date" is 1969 but then check only for the 12.12.1969 (so you are missing the other 364 days of 1969 ....).

Second thing: why CAST(null as timestamp) ?

Null is just null, so simply return NULL directly and don't lose time trying to cast something that can't be casted ...

Or even simpler, reverse your comparison: " if date <> 1969 then date end " and nothing else ...

3025352

I only want to display the date of '12-12-1969' as 0 or empty. 

Gianni Ceresa

Can you try by adding "DATE" ?

CASE WHEN "Agreement Sent Date"."Date" <> DATE'1969-12-12' THEN "Agreement Sent Date"."Date" END

Gianni Ceresa

3025352 wrote:

Here are the screenshots! I am using the CASE in a column filter

Are you really using that in a filter?

You know what a filter is, right?

In SQL a filter is a condition supposed to be resolved into TRUE or FALSE to include or exclude results from the resultset ...

So just your CASE WHEN can't be a filter !!!

Where is the condition to make this formula a filter? You are just missing the = or > or < or any other logical operator and the value on the other side.

Don't you want to use this in the formula column? Or do you try to exclude from your analysis the rows with date = 1969-12-12 ? In that case get rid of your CASE when and just use a simple condition: Date <> 1969-12-12 (no need of an advanced SQL filter for that)