Re: Coalesce Time or TimeStamp

On 23-Jun-2017 09:18 -0600, Dick Johnson wrote:

I am using embedded SQL to extract a data set. I am having a terrible
time figuring out how to coalesce a timestamp or even a time.

FWiW, if an SQL issue occurs irrespective the use of embedded, then the issue is inherently not a HLL [in this case, RPG] issue; SQL issues are discussed on Midrange-L

I started with the timestamp and had no luck. I split the field into
a date and time. Now, the date works but the time does not.

I am doing a Left Outer Join and may not get Records. I keep getting
the same error message - chop the 01.01.00 to 01.01 but no luck.

That change makes the literal/constant value a valid numeric, and likely just changed the effect ["no luck" is not very descriptive] from the sqlcode -104 to -171.?

When I search I only get DATE. I cannot find examples to TimeStamp
or Time.

COALESCE, VALUES, and IFNULL care only about the consistency and/or compatibility of data type for the operands [ref: SQL Reference "Assignments and comparisons"]; thus, an example of any one data type is really just as good as another, wherein all operand data types specified in the function are the same.

(Run SQL Scripts)

Select
A.TANUM, [...]
COALESCE(D.IQCCHGDT,DATE(1990-01-01)),
COALESCE(D.IQCCHGTM,TIME(01.01.00)),
COALESCE(D.IQCSTAT,' ')
[...]

Message: [SQL0104] Token .00 was not valid. Valid tokens: ) ,.
Cause . . . . . : A syntax error was detected at token .00.
Token .00 is not a valid token.
A partial list of valid tokens is ) ,.
This list assumes that the statement is correct up to the token

The same error would be seen using the simple query shown just below; a numeric constant [aka literal] must have only one decimal point, so apparently, despite the context, the parser presumes what appears to be a numeric literal, either should be separated by a comma or terminated/completed by the right parenthesis, instead of inferring the issue is for lack of an apostrophe as a string-delimiter:

values( time( 01.01.01 ) ) /* fails with above -104 */

So the issue is, that character strings must be delimited. And, that literal [aka constant] date/time/timestamp values must be written as strings; e.g. DATE'1990-01-01', DATE('1990-01-01'), TIME'01:01:01', TIME('01.01.01'). Undelimited, instead of being [understood by the SQL to be] a string, the following is understood by the SQL to be an [arithmetic] expression that evaluates to 1988; i.e. nineteen hundred ninety, minus one, minus one: 1990-01-01

The following simple query would fail with a data mapping error [msg CPF5035 RC18], because DATE(1988) can not be evaluated:

values( date( 1990-01-01 ) )