Concatenate with parentheses around a column

[JustATheory]

I have a concatenation problem. I can get the left parentheses to display but not the right. Here is what should happen, if column 2 is populated the concatenate col1 with parens around col2. If col2 is null then concatenate col1 with parens around a dash. It functions properly for placing the das in the field, I’m just having trouble closing the paren. Help is always greatly appreciated. Please see example.


Thanks,
JustATheory


col1 || '('|| NVL(col2,'-') || ‘)’ || as NewCol,

Result should be:

Rec NewCol

1 Data1 (Data2)
2 Data1( - )

 

[JarlH]

If you want to write ANSI SQL, use [tt]COALESCE[/tt] instead of NVL.

Your concatination seems to need a minor correction, remove the last ||, i.e.[tt]
col1 || '(' || COALESCE(col2,'-') || ')' as NewCol,[/tt]

 

[JustATheory]

Hi JarlH,

That was it! Many thanks!


Thanks,
JustATheory