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

On 11-Dec-2015 16:24 -0600, Stone, Joel wrote:

<<snipped>>

On 12/10/2015 4:36 PM, 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?

<<snipped; explained that AS _names_ column vs giving LABEL>>

I should have explained the situation better.

I am using QMQRY to an *OUTFILE.

So the easiest method may be to have a second QMQRY to do the "Label
On" to define the colhdg values.

The easiest might be, to just assign the desired column heading to the field A0182_PRICED_QTY of the queried file; i.e. if that file does not already have a column heading [aka LABEL] assigned for that column and the proposed value is acceptable there as well, and then simply do *not* rename the column with the AS-identifier clause. That is because the query engine will /carry/ the ColHdg info from the field in the original\queried file for any field name alone as the expression on the select column-list\expression-list.

FWiW: A script follows [intended to be run in Run SQL Statement (RUNSQLSTM) statement processor, interactively], showing how the _original_ column heading from the file being queried will be carried into the Output File, if the AS clause to name the column of the query is omitted; the commented lines can be un-commented, to include showing the /renamed/ behavior, and to include two additional ways to view the effects on the OutFile beyond just the [default form] Start QM Query:

drop table qtemp/mysrc ;
call qsys2.qcmdexc('crtsrcpf qtemp/mysrc rcdlen(91) mbr(myq)') ;
insert into qtemp/mysrc (srcdta) values
('select q.A0182_PRICED_QTY ')
,(' as "Net Bus/Lbs" ')
,('from qtemp/atst as q ') ;
drop alias qtemp/atst ;
drop table qtemp/atst ;
create table qtemp/atst
( A0182_PRICED_QTY for qty dec(7, 2) ) ;
label on column qtemp/atst
( A0182_PRICED_QTY IS 'Original Heading' ) ;
insert into qtemp/atst values(7.2) ;
-- call qsys2.qcmdexc('crtqmqry qtemp/myq1 qtemp/mysrc srcmbr(myq)') ;
-- call qsys2.qcmdexc
-- ('strqmqry qtemp/myq1 outfile(qtemp/outtst1) output(*outfile)') ;
-- call qsys2.qcmdexc('dspffd file(qtemp/outtst1)') ;
-- call qsys2.qcmdexc('runqry qryfile(qtemp/outtst1)') ;
delete from qtemp/mysrc where srcdta like ' as%' ;
call qsys2.qcmdexc('crtqmqry qtemp/myq2 qtemp/mysrc srcmbr(myq)') ;
call qsys2.qcmdexc
('strqmqry qtemp/myq2 outfile(qtemp/outtst2) output(*outfile)') ;
-- call qsys2.qcmdexc('dspffd file(qtemp/outtst2)') ;
-- call qsys2.qcmdexc('runqry qryfile(qtemp/outtst2)') ;
drop table qtemp/atst ;
create alias qtemp/atst for qtemp/outtst2 ;
call qsys2.qcmdexc('strqmqry qtemp/myq2 output(*)') ;

Is it possible to do this in one QMQRY source file?

No. A QMQRY is limited to just one statement.

No biggie to add a second QMQRY run, but I was hoping that I could
get away with something as simple as using the word "AS" to rename
the field, hoping I could use "Label On" in the select statement
somehow to define the colhdg.

If an alternative of all upper-cased and underscore instead of blanks, then possibly the option of coding the following [my perhaps incorrect presumption, that the slash implies "or"], for which the column heading result would be NET_BUS_OR_LBS for the column name and thus the implicit heading:

q.A0182_PRICED_QTY as net_bus_or_lbs