Re: Coalesce Time or TimeStamp

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Dick,

If you want to COALESCE to a "initial" value, you could try:

select
coalesce(time_col, time('00.00.00')) AS TIME_VAL ,
coalesce(timestamp_col, timestamp_iso('0001-01-01'))  AS TSTAMP_VAL
from my_table

HTH,

Luis


On Fri, Jun 23, 2017 at 11:28 AM Dick Johnson <dickjaz@xxxxxxx> wrote:

> 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