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

Rob / Chuck

Below resulted in an error.
SELECT "pflib", "pffile", Date-field,count(*) FROM Library/PFfile GROUP BY Date-field ORDER by Date-field.
replaciing pflib and pffile with their actual names.

Column or global variable STMTM3P not found.

Chuck's ovrprtf worked like a champ.

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

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

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, September 27, 2016 12:54 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 10:58 -0500, Steinmetz, Paul wrote:

I'm documenting some PF from a 3rd party app.

I'm running an SQL over the PF, tabling and counting the records by
date.

SELECT Date-field,count(*)
FROM Library/PFfile
GROUP BY Date-field
ORDER by Date-field.

Is there a way to mark/tag the resulting spoolfile with the PF file
name?

Currently, no way to know which PF the spoolfile is for. Possibly
using the User Data.

Device or Total
File Queue User Data Status Pages
QPQUPRFIL PAULS HLD 44
QPQUPRFIL PAULS HLD 115
QPQUPRFIL PAULS HLD 16

Before each SQL request that is using the Printer File (PRTF) named QPQUPRFIL [per use of the Query/400 report writer feature; ¿apparently then, but unstated, the query requests are being made from the Start Interactive SQL Session (STRSQL)?], issue the following CL request to set the User data (USRDTA) value for the spooled file that will be produced for the upcoming SELECT query:

OVRPRTF QPQUPRFIL USRDTA(PFfileName) OVRSCOPE(*JOB)

FWiW, my preference is to use the Spool File Name (SPLFNAME) parameter specification instead, reserving the user-specified data value for other purposes\data [namely, for the Delete Spooled File (DLTSPLF) with the Select Files For (SELECT) parameter specifications]:

OVRPRTF QPQUPRFIL SPLFNAME(PFfileName) OVRSCOPE(*JOB)

Note: Because the interactive SQL request processor does not have\offer a customized Override [stored] procedure [e.g. an OVERRIDE_TO_PRINTER_FILE procedure] from which an override can be established at the call-level of the invoker, an override request that is scoped to a Call Level [e.g. via CALL QSYS2.QCMDEXC ('OVRPRTF . OVRSCOPE(*CALLLVL)')] would implicitly disappear upon return to the session statement-entry, thus why I show OVRSCOPE(*JOB). And AIUI, because the STRSQL as interpreted statement processor and the Query/400 report writer [utilized by the STRSQL feature] do not run in an activation group [definition] that could be established for\with the request to override the PRT, the OVRSCOPE(*ACTGRPDFN) will have the same effect as scoping to the Call Level irrespective the activation group used to invoke the STRSQL [but I have not tested that].

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.