Re: rpg Embedded SQL: date/week order by

Should the XDTE column not be in the result set either? I think so it have to, as that is used to ORDER BY.

Perhaps add a YEAR(XDTE) in your result set and use its ordinal number in the result set for ordering (ORDER BY 1 means ORDER BY column 1 of the result set)

Regards,
Carel Teijgeler

Op 20-2-2016 om 22:51 schreef Booth Martin:

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