Archives of the TeradataForum

Message Posted: Mon, 11 Aug 2003 @ 17:45:33 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have the below sql script in an MLOAD program, which errors out ...

while the same query in bteq/queryman runs fine..

Can anyone help me out here please...

I greatly appreciate help

thanks...

Sankar

     UPDATE TAB_A
     SET COL1 = :IN_COL1
     WHERE CAST((COALESCE(BirthDate,DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8))
                       <> COALESCE(:IN_BIRTHDATE,(CAST((DATE (FORMAT 'YYYYMMDD')) AS CHAR(8))))
     OR    CAST((COALESCE(signUpDate,DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8))
                       <> COALESCE(:IN_SIGNUPDATE,(CAST((DATE(FORMAT 'YYYYMMDD')) AS CHAR(8))))
     ) ;

The bind variables are defined as char datatypes which are being read in from a fixed format flat file.

**** 13:25:57 UTY0805 RDBMS failure, 3617: FORMAT 'YYYYMMDD' does not match the datatype.

where as the same sql in BTEQ/QueryMan runs fine... I replaced the bind variable with constant values...

/**** bind variables replaced with null ***/
select *
from TAB_A
where
        CAST((COALESCE(BirthDate,DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8))
                  <> COALESCE(null,(CAST((DATE (FORMAT 'YYYYMMDD')) AS CHAR(8))))
and     CAST((COALESCE(signUpDate,DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8))
                  <> COALESCE(null,(CAST((DATE(FORMAT 'YYYYMMDD')) AS CHAR(8))))
;

/**** bind variables replaced with blank ***/
select *
from TAB_A
where
        CAST((COALESCE(BirthDate,DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8))
                  <> COALESCE(' ',(CAST((DATE (FORMAT 'YYYYMMDD')) AS CHAR(8))))
and     CAST((COALESCE(signUpDate,DATE) (FORMAT 'YYYYMMDD')) AS CHAR(8))
                  <> COALESCE(' ',(CAST((DATE(FORMAT 'YYYYMMDD')) AS CHAR(8))))
;

	<Prev	Next>		<<First	<Prev	Next>	Last>>