devanshu singhal devanshu singhal - 4 years ago 154
SQL Question

return more than one row as comma separated values from case when statement

Trying to create a function in Postgresql with 4 arguments that returns a table(multiple rows).

in_customer_id, in_start_date, in_end_date, in_risk_flag

The sql query I am using in that function is:

select * from customer as k
where k.customer_id IN (case when $1 = 0 then (select distinct(customer_id) from customer)
when $1 != 0 then $1
and k.start_date >= $2
and k.end_date <= $3
and k.risk_flag IN (case when $4 = 0 then (select distinct(risk_flag) from customer)
when $4 != 0 then $4

Error I am getting is error [21000]:
more than one row returned by subquery used as an expression

Is there any way to get return from case statement as (1,2,3,4,5,6) (comma separated values) instead of a column with multiple rows?

Answer Source

First: distinct is not a function. Writing distinct (customer_id) makes no sense. And in a sub-select that's used for an IN condition the distinct is useless anyway.

It seems that you want to select a specific customer in case you pass a parameter, otherwise you want to select all of them. As far as I can tell, you don't need a sub-select for that. Something like that should do it:

where k.customer_id = case 
                         when $1 <> 0 then $1 
                         else k.customer_id

It essentially turns the condition to where customer_id = customer_id when the first parameter is passed as 0 (although you should better use a null value for that, rather then a "magic" value like zero)

This assumes that customer_id is defined as NOT NULL otherwise this will not work.

You can apply the same pattern for risk_id (again: it will only work if risk_id can not contain NULL values).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download