Re: rpg Embedded SQL: date/week order by

On Feb 21, 2016, at 4:29 AM, Peter Dow <petercdow@xxxxxxxxx> wrote:

Hi Booth,

Maybe this?

'select'
+ 'Year(XDTE),'
+ ' 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 Year(XDTE), Week(Dec((19000000 + XDTE), 8, 0)'
+ ' concat ''000000'')'
+ ' order by XDTE desc';

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 2/20/2016 1:51 PM, Booth Martin wrote:
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';

On 2/20/2016 3:31 PM, Alan Shore wrote:
I am not too sure what poibt you are trying to maje but i belive you have to use the year of the date as well
Maybe if you posted the sql it might help

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.