Re: Coalesce Time or TimeStamp

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

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.

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



(Run SQL Scripts)

Select A.TANUM, A.TACONT, A.TAMODL, A.TATYPE , A.TAORGD, A.TAOQTY,
A.TAORDL,
A.TAVEND, A.TAORDN,

A.TASAMP, A.TARETN, A.TABRCH, B.TDUOM, B.TDGTIN,

B.TDLENG, B.TDWIDT, B.TDHEIG, B.TDWEIG, B.TDQUAN,

B.TDESTF, C.SKDESC, C.SKUPC, C.SKUM, C.SKMOQT,

C.SKLTLO,

COALESCE(D.IQCCHGDT,DATE(1990-01-01)),

COALESCE(D.IQCCHGTM,TIME(01.01.00)),

COALESCE(D.IQCSTAT,' ')

From TWINDEV.TPLATRD AS A

Join TWINDEV.TPLDIM AS B ON A.TAMODL = B.TDMODL

Join TWINDEV.TPLSKU AS C ON A.TAMODL = C.SKMODL

Left Outer Join JOHDICLIB.TPLIQC AS D ON A.TANUM =
D.IQCNUM
AND A.TACONT = IQCCONT ;





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



D.J.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD