concatenating case statements 1

[10PoundBrown]

Hi All, thanks for reading my post. I am using a Postgres 7.3 and I have a query that contains lots of empty values, not null really, white space (the php code isnt mine)

Anyway, Im trying to format a report and I want to concatenate commas between my case statements like so

select id||', '||
base_lagtype_id||', '||
status_id||', '||
base_number||', '||
base_name||', '||
base_preds||', '||
base_duration||', '||
case
when base_start_date = '' then 'null'
else base_start_date
end as base_start_date||', '||
case
when base_end_date = '' then 'null'
else base_end_date
end as base_end_date
from sch_schedule_tasks
order by id

Unfortunately I get parse errors back. Anyone point me in the right direction for concatenating between case statements? Thanks

 

[10PoundBrown]

wow, it appears all I have to do is post and the answer will come to me.

when I end my case argument, I had
as <column_name)>||', '||

which causes the sql to parse error, remove that and add the cat string and blammo, works fine for what I needed. Thanks.

 

[swampBoogie]

select id||', '||
base_lagtype_id||', '||
status_id||', '||
base_number||', '||
base_name||', '||
base_preds||', '||
base_duration||', '||
case
    when base_start_date = '' then 'null'
    else base_start_date
    end || ', '||
case
    when base_end_date = '' then 'null'
    else base_end_date
    end as base_end_date
from sch_schedule_tasks
order by id

That would work if all columns are character. If you have any non character data you must cast them before concatenating. E.g.

select cast(id as varchar(10)) || ',' ...

Btw, it is not a case statement, it's a case expression. A case statement in SQL is something else.

 

[10PoundBrown]

Thanks for the heads up and advice swampboogie!!

 

[r937]

if the php code isn't yours, be careful -- i know you said the values were &quot;not null really&quot; but if any one of them is, the whole row of concatenated vallues will be

rudy

http://r937.com/

SQL Consulting

 

[10PoundBrown]

WORD, yeah Im running my SQL straight into the backend so Im not touching any of the php code but thanks for the heads up.