Use of Case with Select and Dropdown

Queries and Resources
1

Hi

This code snippet is intended to take a value provided by a dropdown and use that value to decide whether to select past or future dates from the table. Before even discovering whether case will work in this scenario we have the problem of what cunning syntax is required to get the test criteria to evaluate to True or False. If anyone has a quick answer to that it would be a great start.

Thanks in anticipation...

select * from xone
WHERE
CASE
WHEN {{select1.value}} = "PAST" THEN xone.date < {{moment()}}
WHEN {{select1.value}} = "FUTURE" THEN xone.date > {{moment()}}
ELSE xone.date
END;

2

Hey patient!

Depending on what you want to accomplish you can use the following code:

SELECT *,
CASE
WHEN {{select1.value}}='PAST' THEN createdAt<{{moment().format('YYYY-MM-DDTHH:mm:ss')}}
WHEN {{select1.value}}='FUTURE' THEN createdAt>{{moment().format('YYYY-MM-DDTHH:mm:ss')}}
ELSE createdAt END AS grouping_date
FROM {{table1.data}}

image
image1380×1198 104 KB

Your CASE statement needs to be before WHERE. Based on the output of grouping_date you can filter your dataset and display what you need.

Hope this helps.

3

Thank you. This looks really cool, will let you know how I get on.
One question, does the variable grouping_date have any function outside the CASE statement?

4

Hi, my amended code:

select
CASE
WHEN {{select1.value}} = 'PAST' THEN xone.date < {{moment().format('YYYY-MM-DDTHH:mm:ss')}}
WHEN {{select1.value}} = 'FUTURE' THEN xone.date > {{moment().format('YYYY-MM-DDTHH:mm:ss')}}
ELSE xone.date
END AS date_range,
xone.name,
xone.id
FROM xone;

produces this error:

  • error:true
  • message:"CASE types timestamp with time zone and boolean cannot be matched"
  • position:35
5

grouping_date could be used to filter out what you need or transformed using different queries.

The error you're getting is because your date is timestamp, while case outputs boolean in first two cases which is of a different type.

You can CAST() all those values to a string which should help you avoid the type issue.

SELECT *,
CASE
WHEN {{select1.value}}='PAST' THEN CAST(createdAt<{{moment().format('YYYY-MM-DDTHH:mm:ss')}} AS string)
WHEN {{select1.value}}='FUTURE' THEN CAST(createdAt>{{moment().format('YYYY-MM-DDTHH:mm:ss')}} AS string)
ELSE CAST(createdAt AS string) END AS grouping_date
FROM {{table1.data}}

Otherwise you need to decide what your cases output.

1 Like