Re: ACS Run SQL Scripts user custom examples

On 29-Mar-2017 16:31 -0500, Rob Berendt wrote:

On 29-Mar-2017 15:21 -0500, Tyler, Matt wrote:

I just realized that with at least ACS 1.1.6.2, maybe less, that
you can build your own examples to be inserted into your SQL code.
So if you have a certain set of parameters for function,
procedures, etc. you can think of these like the RDi templates or
snippets.

Find your ACS user folder where your 5250 configuration is located
and then go into the RunSQLScripts\Examples folder and there should
be a TEST.SQL. The name is not that great but the name is not
important. You can have as many examples you want. Now if we could
just get ACS to follow a link to another folder for a common set of
use examples plus the local user set we would have something.

Thanks for the tip.

I just created one using this
-- category: Custom
-- description: Subtotals - With Grouping sets
--
-- Sample with "Detail", subtotal, grand total
-- The trick is the detail is really a subtotal for each row
-- If you don't want detail, omit
-- Any columns selected which are not part of any grouping set, or
aggregate (like sum)
-- The grouping set below flagged as "Detail"
--
SELECT OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME
, SPOOLED_FILE_NAME, sum(SIZE) as size, sum(TOTAL_PAGES) as pages
, JOB_NAME, CREATE_TIMESTAMP
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
group by grouping sets (
(OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME,
JOB_NAME, CREATE_TIMESTAMP) -- "Detail"
,(OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME) -- subtotal
,() -- Grand Total
)
order by OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME
, SPOOLED_FILE_NAME, JOB_NAME, CREATE_TIMESTAMP;



I just discovered you 'CAN' have multiple files in that Examples folder!
I have two files: crude.sql, subtotal.sql
It will only refresh the "insert from examples - custom" at startup, so
restart run sql scripts or start a new session of it.
Start your comments with lines like the following three: category,
description, blank line. For example:
-- category: Custom
-- description: Subtotals - using Grouping sets
--

You lose all your formatting that you store in your example.
You also lose all your comments that you stored in your example.

Help me work through this.

Just an FYI about the above quoted message, for which the archived copy can be found at (https://archive.midrange.com/midrange-l/201703/msg00871.html):

A line starting with two dashes and a space are treated as an effective End-Of-Message [more appropriately, the Start-Of-Signature] indicator for archival purposes, and so too by many newsreaders; e.g. in mine, I had to perform an additional copy/paste because the reply-to did not automatically capture any of the text data past the line with that combination of characters. Thus, the quoted text above is conspicuously missing from the archived version [again, see above link] of the original message.