Re: TIMESTAMPDIFF Not Found

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

There are all manner of problems here - starting with the DATE function 
- it has only one parameter - perhaps you are confusing it with the CHAR 
function.

Get out the manual and check out the syntax for what seems wrong - 
actually, for everything. I think you could benefit by running it in 
STRSQL - then, when you get some errors, you can do the usual F1 for 
help routine and get more information - MAYBE!

Vern

On 1/26/2011 7:22 AM, Michael Ryan wrote:
> I'm trying to debug a colleagues SQL statement, and when I put it into
> Visual Explain, I'm getting an error that TIMESTAMPDIFF can't be
> found. I'm also getting errors that the date format identifier
> (yyyymmdd) is not in any of the tables. Is there a format identifier
> on the date function? I'm playing around with the statement, removing
> and adding to see if I can get it to work. Here's the original SQL
> statement:
>
> SELECT  date( POSEVTP.EVDATE, yyyymmdd ) AS EVDATE,
> XMSTNO,
> XMINNO,
> XMINVT,
> POSEVTP.EVUSER,
> POSEVTP.EVBEFR,
> POSEVTP.EVAFTR,
> POSEVTP_1.EVAFTR AS EVAFTR_1,
> POSEVTP_1.EVUSER AS EVUSER_1,
> TIMESTAMP( date( POSEVTP.EVDATE, yyyymmdd ), substr( digits(
> POSEVTP.EVTIME ), 1, 2 ) || ':' || substr( digits( POSEVTP.EVTIME ),
> 3, 2 ) || ':' || substr( digits( POSEVTP.EVTIME ), 5, 2 ) ) AS
> COLUMN0003,
> TIMESTAMP( date( POSEVTP_1.EVDATE, yyyymmdd ), substr( digits(
> POSEVTP_1.EVTIME ), 1, 2 ) || ':' || substr( digits( POSEVTP_1.EVTIME
> ), 3, 2 ) || ':' || substr( digits( POSEVTP_1.EVTIME ), 5, 2 ) ) AS
> COLUMN0002,
> FLOAT( TIMESTAMPDIFF( SQL_TSI_SECOND, ( TIMESTAMP( date(
> POSEVTP_1.EVDATE, yyyymmdd ), substr( digits( POSEVTP_1.EVTIME ), 1, 2
> ) || ':' || substr( digits( POSEVTP_1.EVTIME ), 3, 2 ) || ':' ||
> substr( digits( POSEVTP_1.EVTIME ), 5, 2 ) ) ), ( TIMESTAMP( date(
> POSEVTP.EVDATE, yyyymmdd ), substr( digits( POSEVTP.EVTIME ), 1, 2 )
> || ':' || substr( digits( POSEVTP.EVTIME ), 3, 2 ) || ':' || substr(
> digits( POSEVTP.EVTIME ), 5, 2 ) ) ) ) ) / FLOAT( 60 ) AS COLUMN0005,
> XMRETL
> FROM
> NEWSYS.POSEVTP POSEVTP,  NEWSYS.POSMSTP POSMSTP,  NEWSYS.POSEVTP POSEVTP_1
> WHERE
> POSEVTP.EVTRAN=POSMSTP.XMTRAN AND
> POSEVTP.EVTRAN=POSEVTP_1.EVTRAN AND
> POSEVTP.EVAFTR=POSEVTP_1.EVBEFR AND
> ( POSEVTP_1.EVAFTR = 'I' AND POSEVTP.EVDATE = 20041219 )
>
> Any help appreciated. Thanks!