RE: SQL output to Printer - anyway of marking/tagging the spoolfile with the PF name -- MIDRANGE-L

Rob,

Very nice.

Now, if I knew the name of the date field for all the files, I could submit this to batch, substituting the 3 variables.

Library
File
Date

RUNSQL REQUEST(' select ''UPPENFILES'' as MyLib, ''STMTM3P'' as MyFile, GQBGUN, count(*) as MyCount FROM UPpenfiles/STMTM3P GROUP BY GQBGUN ORDER by GQBGUN') OUTPUT(*PRINT)

Run it across an entire library.

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Tuesday, September 27, 2016 2:15 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL output to Printer - anyway of marking/tagging the spoolfile with the PF name

try this
select 'UPPENFILES' as MyLib,
'STMTM3P' as MyFile,
GQBGUN,
count(*) as MyCount
FROM UPpenfiles/STMTM3P
GROUP BY GQBGUN
ORDER by GQBGUN

This will give you nicer column headings.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: "'midrange-l@xxxxxxxxxxxx'" <midrange-l@xxxxxxxxxxxx>
Date: 09/27/2016 01:53 PM
Subject: RE: SQL output to Printer - anyway of marking/tagging the
spoolfile with the PF name
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

The "double quotes" should have been 'single quotes'

Liking a combination of Rob and Chuck's solution better.

Spoolfile is marked/tagged, also included within the spoolfile.

OVRPRTF FILE(QPQUPRFIL) SPLFNAME(STMTM1P) OVRSCOPE(*JOB)

select 'UPPENFILES','STMTM3P' , GQBGUN,count(*) FROM UPpenfiles/STMTM3P
GROUP BY GQBGUN ORDER by GQBGUN

Device or Total Current
File Queue User Data Status Pages Page Copies
QPQUPRFIL PAULS HLD 16 1
STMTM1P PAULS HLD 16 1

09/27/16 13:44:25 PAGE 1
Constant value Constant value Stmt Exp COUNT ( * )
Due Date
UPPENFILES STMTM3P 0 3
UPPENFILES STMTM3P 1010330 33,745
UPPENFILES STMTM3P 1010406 47,827

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Tuesday, September 27, 2016 1:23 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL output to Printer - anyway of marking/tagging the
spoolfile with the PF name

On 27-Sep-2016 12:09 -0500, Steinmetz, Paul wrote:

[...]
SELECT "pflib", STMTM3P, Date-field, count(*) FROM Library/PFfile
GROUP BY Date-field ORDER by Date-field [...]

Above statement was redacted; this text replaces the comment that
explained that\how the original statement from the OP was not the actual
statement with the following error msg SQL0206:

Column or global variable STMTM3P not found.

The suggested value was shown to be coded as a string-constant [aka a
string-literal] value, for which the apostrophe as delimiter is
required; for file named STMTM3P in library PFLIBNAME, the following
request should function without error:

SELECT 'PFLIBNAME', 'STMTM3P', Date_field, count(*)
FROM PFLIBNAME / STMTM3P
GROUP BY Date_field
ORDER by Date_field

Note: the value could be established as\from a variable, as an
alternative way to resolve the sqlcode=-206; ask if that is of interest
and not understood