MVSFORUMS.com

NASCAR9 · Posted: Fri Apr 17, 2009 11:50 am Post subject: Output from Batch SQL

Is it possible to output spaces instead of low values when running SQL in a batch job? We are running DB2 V9 CM

The output below is B.PAT_LNAME and B.PAT_FNAME and looks like this:

NASCAR9 · Posted: Fri Apr 17, 2009 12:24 pm Post subject:

The above columns are VARCHAR
_________________
Thanks,
NASCAR9

kolusu · Posted: Fri Apr 17, 2009 12:29 pm Post subject:

NASCAR9,

Well RTRIM returns a varchar string so you see low values in them, but is your intention to trim the spaces and have the last and first names together?

like

NASCAR9 · Posted: Fri Apr 17, 2009 1:01 pm Post subject:

kolusu I tried 'RTRIM' as a test and I failed. The output columns are fixed length. The columns are defined as VARCHAR and when I code this :

dbzTHEdinosauer · Posted: Fri Apr 17, 2009 1:12 pm Post subject:

don't know quite how to write the sql, don't have any testing facility, but,

using substr(column,1,locate(column,x'00')) might get it.

you can substitute instr for locate.
_________________
Dick Brenholtz
American living in Varel, Germany

kolusu · Posted: Fri Apr 17, 2009 1:16 pm Post subject:

NASCAR9,

You still did not answer my question as to if you need to concatenate last name and first name together. You need CHAR function on the string to have spaces instead of low-values. Anyway you can use one of the following depending on your need

NASCAR9 · Posted: Fri Apr 17, 2009 1:33 pm Post subject:

kolusu, I'm sorry I thought I was clear when I said 'Fixed Length' but I can see where it was confusing. The columns are seperate like your second example.
And THANK YOU! You once again had the correct solution! You can see the colunm with the 'CHAR' function and the column without it.