Re: rpg Embedded SQL: date/week order by

[Michael Schutte <mschutte369@xxxxxxxxx>]

It appears xdte isn't a true date field.  Cyymmdd numeric field. 



Sent from my iPhone

> 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.