Re: SQL list *OUTFILE of DSPOBJD by date

On 12 Apr 2013 10:34, rob@xxxxxxxxx wrote:

I kind of like Chuck's approach to this. Present the list in human
readable, but order it in the proper date format. IOW

select odlbnm, odobnm, odobtp
, odcdat as CreateDate, odudat as UsedDate, odobtx
from ...
order by
odxcen concat substr(odxdat,5,2) concat substr(odxdat,1,4) desc
-- substituting x for the selected ordering format.

Yep. I presume most humans should [still] be able to deal with the MMDDYY, and esp. so, if the column label includes a hint; e.g.:
... , ODCDAT as "Created:MDY", ODUDAT as "LastUse:MDY", ...

FWiW in contrast to the above ORDER BY, I had replaced each CONCAT operator with a DESC [descending] keyword followed by a comma, because there is no reason to ask the SQL DB to perform the concatenation of the result of each expression, when each expression alone can be the ordering. That is because I believe the processing [i.e. the code generated] for the SUBSTR expression is a quite literal translation into byte positions, and the code for the CONCAT expression probably is also a quite literal translation.

What I mean to suggest, is, that the following sets of work are different; if you can make any sense of it. The implication being that the latter is unnecessarily complex, and I am not sure how much there is in the way of optimizing to make the latter operate the same as the former. The byte positions are representative of the buffer, as seen by DSPFFD QADSPOBJ, for the columns ODCCEN and ODCDAT, as parsed by the expression in the ORDER BY from the quoted message; the first for when replacing the CONCAT with DESC, and the latter for the unchanged ORDER BY:

order by bp:111 desc, bp:116-117 desc, bp:112-115 desc

order by temp_result = [bp:111 cat bp:116-117 cat bp:112-115] desc