I have created a CTE using 'with' clause and I am using case when expression to populate a column in CTE and I wanted to use the result of case when expression in where condition as a criteria value to fetch the results using 'in' operator.
When there are values such as Range1 or Range2 which are matching the column values of table the query is producing proper result. I wanted to fetch results based on combination of values such as Range1,Range2 or Range3,Range4 using in operator in the where clause of postgreSQL. I have tried creating below case when and it does not give me results.
with sampleq as(
select case when trim(to_char(current_date,'Day')) like 'Monday' then '''Range1'',''Range2'',''Range3'''
when trim(to_char(current_date,'Day')) like 'Tuesday' then '''Range4'',''Range5,''Range6'''
when trim(to_char(current_date,'Day')) like 'Wednesday' then '''Range7'',''Range8,''Range9'''
when trim(to_char(current_date,'Day')) like 'Thursday' then '''Range10'',''Range11'''
when trim(to_char(current_date,'Day')) like 'Friday' then '''Range12'',''Range13'''
else ' ' end as Rangecriteria
,Column1,column2 from sampletable
)select * from sampleq where column2 in (Rangecriteria)
Usually, if I use strings that are seperated by comma 'in operator' works fine. But if I use column to populate the value based on condition and use it 'in operator' it does not work. Could you please help me finding a solution here?
Thanks in advance, S Ch Avinash
Your approach doesn't work because you're essentially expecting PostgreSQL to treat strings as code. How could PostgreSQL know whether '1, 2'
means you want to find rows with numbers 1
and 2
or literal 1, 2
text?
The most obvious solution is to have a master table with all these range definitions. If you don't want a table, you can also emulate it with a CTE:
WITH range_definition (weekday, "range") AS (
SELECT 'Monday', 'Range1'
UNION ALL SELECT 'Monday', 'Range2'
UNION ALL SELECT 'Monday', 'Range3'
UNION ALL SELECT 'Tuesday', 'Range6'
-- ...
), DATA (weekday, column2) AS (
SELECT 'Monday', 123
UNION ALL SELECT 'Tuesday', 456
)
SELECT *
FROM DATA
INNER JOIN range_definition ON DATA.weekday = range_definition.weekday;
Then, you can use regular queries with regular IN
operator.