Re: rpg Embedded SQL: date/week order by

Hi Booth,

Typically, your ORDER BY columns / expressions will match your GROUP BY
columns / expressions. So, if you GROUP BY WEEK, you'll ORDER BY WEEK. If
you GROUP BY YEAR, WEEK, you'll ORDER BY YEAR, WEEK (perhaps with DESC on
both to show most recent first). Typically, every column or expression in
your GROUP BY, will also be specified in your SELECT clause.

Columns / expressions NOT in your GROUP BY, need to be aggregate columns in
your SELECT column / expression list. So, your references to XLOC, XAQT,
XORD, XQTY need to be enclosed in aggregate functions like SUM or COUNT or
AVG. Your statement isn't working because you didn't aggregate columns not
specified in your GROUP BY.

If XLOC represents a "location", I suspect you want XLOC and a calculated
YEAR in your GROUP BY, like this XLOC, YEAR(), WEEK(). Or perhaps GROUP BY
YEAR(), WEEK(), XLOC. Your measurement columns look like XAQT, XORD, XQTY,
so those likely need to be inside SUM(). If XORD is an order number, I
suspect you want to remove that column entirely. Incorporating an order
number into a summary report doesn't make sense, unless your're simply
counting order detail lines via COUNT( XORD ), or distinct order numbers
via COUNT( distinct XORD ).

When I find myself about to duplicate an expression inside a query, I code
it like this to eliminate redundant calculations, and to improve
readability:

select WEEK_#, ...
from MY_TABLE MT
cross join lateral (
values Week(Dec((19000000 + MT.XDTE), 8, 0)
) as W ( WEEK_# )
group by WEEK_#
order by WEEK_#

CROSS JOIN LATERAL to a VALUES clause in simple terms means "tack a
calculated column onto a result set". This allows the assigned name to be
referenced elsewhere in the query without repeating the calculation.

Regards,

Mike

date: Sat, 20 Feb 2016 15:23:45 -0600

from: Booth Martin <booth@xxxxxxxxxxxx>
subject: rpg Embedded SQL: date/week order by

As you all can no doubt tell, I am enjoying SQL!

In the category of... it'd be nice to fix if its easy but its not worth
a candle...

I am displaying data in order by date, newest first. Works fine when I
am displaying every day. However when I group by week or month it shows
last year December to June first, then this year February to January
because week 52 is greater than week 1. month 12 is greater than month
1. I can not figure out how to order by date because date is not a
group by field.


Here is the prepared statement I would like to use. It does not
compile. It will compile & run if I order by week, but that gives me
the issue of this thread.

'select'
+ ' Week(Dec((19000000 + XDTE), 8, 0) concat ''000000''),'
+ ' XLOC, XAQT, XORD, XQTY'
+ ' from FILEA'
+ ' join FILEB on XITM = XITM2 and XDTE = XDTE2'
+ ' where XITM = ?'
+ ' group by Week(Dec((19000000 + XDTE), 8, 0)'
+ ' concat ''000000'')'
+ ' order by XDTE desc';