Re: ACS Run SQL Scripts user custom examples

On 19-Apr-2017 11:40 -0500, Tyler, Matt wrote:

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

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.

Chuck, I wonder what the insert examples popup looks like for you
with the sample you gave. Outside of the obvious word wrap that
went on with "-- Any columns selected which are not part of any
grouping set, or aggregate (like sum)" I also had to change the
comment style from --(double-dash) to /* */ for the grouping set
comments. Once those two were done it appears in the example pane as
I expect. So there is an expectation of formatting going on.

I am at version 1.1.7.0 of ACS.

I expect Rob could respond; I have inserted his message above to make more conspicuous, that the followup text to the OP was all his.

My reply was just re-posting what Rob had attempted to post, because his post was all but lost to the archives, due to one of the double-dash comments; the comment-line with just one trailing blank. Of course list participants would have seen the entire text in an email; seen too, when obtained from the news server via NNTP, even if a newsReader such as mine would have grayed-out most of his reply. But upon my posting, his text since became visible in the archives -- even if only weeks later.

FWiW, I have never used the "examples" feature of iACS. I just code my SQL how I want, using a plain-text editor, because I have never found any SQL formatting-feature that can easily produce what I prefer; that is especially true of what typically happens with inline comments.