Re: SQL: How to change *COLHDG to include spaces between words

On 10-Dec-2015 16:36 -0600, Stone, Joel wrote:

I would like to add a col hdg to an SQL field in a select statement:

q.A0182_PRICED_QTY as "Net Bus/Lbs"

Unfortunately the iseries SQL engine creates the field with a column
heading which actually contains the double-quotes.

Is it possible to have spaces in the colhdg as one would expect, but
not include any quote characters?

That AS clause is /naming/ the result column, and thus the standard naming-rules apply; i.e. what is specified is not the Column Heading, but the field /name/. To maintain lower-case or to include special characters [including space\blank], the /name/ must be delimited by the double-quote character.

If the Column Heading /must/ appear as shown above, in reporting [in reporting that even supports the ColHdg feature], sans delimiters, then encapsulate the SQL SELECT query in a VIEW, and issue the LABEL ON COLUMN to assign the desired /label/.

create table qtemp/atst
( A0182_PRICED_QTY for qty dec(7, 2) )
;
insert into qtemp/atst values(7.2)
;
create view qtemp/atstvw as
( select q.A0182_PRICED_QTY as "Net Bus/Lbs"
from qtemp/atst
)
; -- select * from qtemp/atstvw report follows:
"Net Bus/Lbs"
7.20
******** End of data
-- the field _named_ with delimiters, gets labeled, without:
label on column qtemp/atstvw
( "Net Bus/Lbs" IS 'Net Bus/Lbs'
)
;
; -- select * from qtemp/atstvw report follows:
Net Bus/Lbs
7.20
******** End of data