DOUBLE Function

General
Enthusiast

DOUBLE Function

Hello, I have been ask to reperform a query which was ran back in 2012.  Unfourtuanlly, when i copy and pasted the query into Teradata, I am getting an "SELECT Failed. [3706] Syntax error: expected something between ',' and the 'DOUBLE' keyword.".  I have never seen this DOUBLE function before, and have no idea how to change it to work.  Could someone help me figure out, why this error is happening, as well as what this piece of code is acctually doing?  The TXN_DESCRIPTION field is a text field which is the transaction description. 

 

A.ACCOUNT_NB,

DOUBLE (REPLACE (TRIM (SUBSTR (TXN_DESCRIPTION, LOCATE('$', TXN_DESCRIPTION) + 1,

CASE LOCATE (' ', SUBSTR (TXN_DESCRIPTION, LOCATE ('$', TXN_DESCRIPTION))) WHEN 0 THEN LENGTH (TXN_DESCRIPTION)

ELSE LOCATE (' ', SUBSTR (TXN_DESCRIPTION, LOCATE ('$', TXN_DESCRIPTION))) -1 END)), ',', '')) AMT,

1 REPLY
Teradata Employee

Re: DOUBLE Function

The original SQL may have been relying on the ODBC driver to convert the syntax before submitting it to the database.

I suspect DOUBLE(...) means CAST(... AS FLOAT); you might also consider NUMBER or DECIMAL data types instead.

For REPLACE; you can use OREPLACE.

For LOCATE, you can use INSTR.

 

LENGTH is now valid Teradata SQL, and SUBSTR and TRIM have always been.