Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Help me identify the query error please.
 
From:   Arunachalam, Sankar

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023