Re: rpg Embedded SQL: date/week order by

[Peter Dow <petercdow@xxxxxxxxx>]

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