On 11-Dec-2015 16:24 -0600, Stone, Joel wrote:
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
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
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)') ;
('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