On 10-Nov-2015 15:08 -0600, rob wrote:
Is there some way to generate the epoch time in SQL?
The following might be of assistance, though other replies might have
already given what is the desired effect.
Nothing to do with epoch, just some comments about the attempts made
with the GENERATE_UNIQUE SQL scalar:
but that was blank.
And I tried the ancient
select generate_unique() from sysibm.sysdummy1
but that worked the same
Probably not /blank/, rather, what was attempted to be displayed by
the report writer were likely _mostly_ non-displayable characters. The
output from the GENERATE_UNIQUE scalar is essentially of BINARY data
type; actually matches the FOR BIT DATA. Typically the output from the
function is only viewed as a character string showing the hexadecimal
representation or the timestamp; i.e. one of:
hex(generate_unique()) or timestamp(generate_unique())
I took a wag at
values cast(generate_unique() as char(30))
but that wasn't any better.
And te effect of casting the effective binary data to a character
data type with encoding is no different for the 5250 report writer; the
Query/400 report writer used for non-DRDA SELECT queries in STRSQL will
try to put the data to the display, and if the WS returns errors, the
data will be translated converting all non-displayable characters to
blanks; though the expected effect if the error occurred from the WS,
would be a QRY1041 "Cannot show this data at the display station." in
the message-line to indicate the effect.
This was a little different
values generate_unique(), timestamp(generate_unique())
******** End of data ***
Before I never even got the plus signs.
The joblog would show what was the failed mapping; if not run in
debug, then possibly only visible in the active joblog while the report
panel /Display Data/ is still active.
Because the two different data types are requested for one column,
the SQL would attempt to choose a compatible type betwixt [IIRC, using
data type _promotion_ rules, just like for an explicit UNION of those
two scalar results]; in this case, apparently the TIMESTAMP was deemed
the data type for the result-column. But given the first value was
effective gibberish, as a character string to represent a TIMESTAMP
value, the character-13 value was unable to be converted into a
timestamp value and thus effected a /data mapping error/ [which in the
Query/400 report writer is manifest by all plus symbols].
I am not sure why however, in any release, that a CHAR(13) FOR BIT
DATA would be considered /compatible/ rather than the effecting an
error; even in 7.2 the minimum is 14-bytes. I am just pointing out what
the effects appear to have been [given the report shown for the query
request shown], not to imply that is the proper effect; that the query
even produced output, rather than issuing an error preventing the query
even starting, may be a defect.