USING COALESCE and || in SQL 1

[DFW1999]

Hi,

this following script is part of a fastExport script which I want to execute in TD. Without using COALESCE I am able to process that script with no error. But if one of the concatenated field is NULL then the whole string becomes null and gives me empty fixed length field.

When I tried to check NULL with Coalesce function then I am getting the following error:

When I am trying to use COALESECE, TRIM and || all together I am getting this following error:
"3707: syntax error expecing something like "IN" between ) and ||'




HERE IS MY query:

SELECT
CASE
WHEN coalesce(TRIM(D.col1),'')||
coalesce(TRIM(D.col2),'')||
coalesce(TRIM(D.col3),'')||
coalesce(TRIM(D.col4),'')||
coalesce(TRIM(D.col5),'')||
coalesce(TRIM(D.col6),''))=''
OR coalesce(TRIM(D.col1),'')||
coalesce(TRIM(D.col2),'')||
coalesce(TRIM(D.col3),'')||
coalesce(TRIM(D.col4),'')||
coalesce(TRIM(D.col5),'')||
coalesce(TRIM(D.col6),'') IS NULL THEN
CAST(' ' as CHAR(30))
ELSE
CAST(SUBSTR(coalesce(TRIM(D.col1),'')||
coalesce(TRIM(D.col2),'')||
coalesce(TRIM(D.col3),'')||
coalesce(TRIM(D.col4),'')||
coalesce(TRIM(D.col5),'')||
coalesce(TRIM(D.col6),'')||' ',1,30) as CHAR(30))
END
FROM TABLE_ADDR D

Any help would be appreciated.
thanks

 

[dnoeth]

You need to place brackets around CASE/COALESCE
(coalesce(...)) || (coalesce (...))

But....
There's no need for the outer CASE at all:

select
CAST(
(coalesce(TRIM(D.col1),''))||
(coalesce(TRIM(D.col2),''))||
(coalesce(TRIM(D.col3),''))||
(coalesce(TRIM(D.col4),''))||
(coalesce(TRIM(D.col5),''))||
(coalesce(TRIM(D.col6),''))
) as CHAR(30))
from tab

Dieter

 

[DFW1999]

Thanks for quick response