oReplace issue with BTEQ

Database
Teradata Employee

oReplace issue with BTEQ

Hi,

I have a stored procedure, the code for which is below:

 

REPLACE PROCEDURE TEMP_TEST (
	IN PAR_BUSN_DT DATE
)
BEGIN
	DECLARE QRY_1 VARCHAR(1000);
	DECLARE QRY_2 VARCHAR(500);
	SET QRY_1 = oREPLACE('FDATE 1 = :PAR_BUSN_DT', ':PAR_BUSN_DT', CAST(PAR_BUSN_DT AS VARCHAR(10)));
	SET QRY_2 = 'FDATE 2 = ' || PAR_BUSN_DT;
	INSERT INTO TEMP VALUES (1, QRY_1);
	INSERT INTO TEMP VALUES (2, QRY_2);
END;

Now when I call the above procedure from SQL assitant, I get the following result in TEMP table:


CALL TEMP_TEST('2018-07-15');
SELECT * FROM TEMP;

 

Result:

    ID    VAL
1    1    FDATE 1 = 2018-07-15
2    2    FDATE 2 = 2018-07-15

 

But if I call the same procedure from BTEQ, I get the following result:

ID VAL
----------- ---------------------------------------------------------------
          1 FDATE 1 = 18/07/15
          2 FDATE 2 = 2018-07-15

 

Why is the date format getting changed in "FDATE 1" when calling from BTEQ?

7 REPLIES
Teradata Employee

Re: oReplace issue with BTEQ

That's because you have implicit conversions :

 Try with :

SET QRY_1 = oREPLACE('FDATE 1 = :PAR_BUSN_DT', ':PAR_BUSN_DT', TO_CHAR(PAR_BUSN_DT, 'YYYY-MM-DD'));
SET QRY_2 = 'FDATE 2 = ' || TO_CHAR(PAR_BUSN_DT, 'YYYY-MM-DD');

 

Teradata Employee

Re: oReplace issue with BTEQ

Thanks for the response.

 

Okay I will try this, but why is it not happening if I call the same procedure from SQLA and why this issue only comes when I call it from BTEQ?

Teradata Employee

Re: oReplace issue with BTEQ

The problem with implicit conversion is that the engine will have to get his conversions parameter from somewhere.

It can be in the ODBC properties, in the SQL Assistant options, or elsewhere.

Teradata Employee

Re: oReplace issue with BTEQ

Okay, if I change the following:

oREPLACE('FDATE 1 = :PAR_BUSN_DT', ':PAR_BUSN_DT', CAST(PAR_BUSN_DT AS VARCHAR(10)));

to

oREPLACE('FDATE 1 = :PAR_BUSN_DT', ':PAR_BUSN_DT', TO_CHAR(PAR_BUSN_DT, 'YYYY-MM-DD'));

 

then the both BTEQ and SQLA is producing the same result. Does TO_CHAR and CAST works differently in the two tools?

Tags (1)
Teradata Employee

Re: oReplace issue with BTEQ

I think if you want to use an explicit format with cast you'd have to do this :

cast(cast(PAR_BUSN_DT as date format 'yyyy-mm-dd') as varchar(10))
Teradata Employee

Re: oReplace issue with BTEQ

The expression, CAST(PAR_BUSN_DT AS VARCHAR(10)), does not specify a format for the date, so BTEQ will use the default format for this session (user-defined or local system default).  SQLA uses ODBC, which has its own notion of what the default date format is.  That is why you see the difference.  Of course, when use the TO_CHAR function the date format is explicitly stated.

Teradata Employee

Re: oReplace issue with BTEQ

Thanks Waldar &  GJColeman for your response.