Insert Label With Field Name in SQL Select

[jtrapat1]

How can I concatenate a label with the value of a field that I'm returning from a SELECT statement in DB2?
Here's my SELECT statement:
The field with the label is: Unknown Agency + fieldname.
------------------------------------------
SELECT nysa.bp10t_appropriation.agency_code, nysa.bp10t_appropriation.agency_code, 'Unknown Agency - ' + nysa.bp10t_appropriation.agency_code, '','', 0, 0,'A', 'DOB Load', CURRENT TIMESTAMP FROM nysa.bp10t_appropriation LEFT JOIN nysa.bp02t_agency ON nysa.bp10t_appropriation.agency_code = nysa.bp02t_agency.agency_code
--------------------------------------------
In SQL Server, this works but I don't know the syntax for db2.

Thanks in Advance
John

 

[MarcLodge]

Hi John,

The concatenation symbol in DB2 is ||

So you would want 'Unknown Agency - '||nysa.bp10t_appropriation.agency_code

Hope this helps
Marc

 

[blom0344]

Hello John,

To expand on Marc's answer:

There are 3 flavors for concatening strings:

'A'||'B'
'A' CONCAT 'B'
CONCAT ('A','B')

Be aware that concatenating CHAR fields leaves trailing blanks in place, whereas concatenating VARCHAR fields remove trailing blanks..........

So possibly for CHAR types you would want:

RTRIM(<field1>)||RTRIM(<field2>) T. Blom
Information analyst
tbl@shimano-eu.com