Case Statement in Fast Export Utility Script

Tools
Enthusiast

Case Statement in Fast Export Utility Script

When I use Case Statement in Fast Export Script, it got failed; Even it works fine if we directly use it in Teradata Sql Assistance.

Please someone tell me why it happens. (Does fast Export not support the Case Clause)

select
(CASE WHEN length(trim(cast(BASE.Link_Prof_center_Hie_cd as CHAR(10))))= 4 THEN '000'||Trim(BASE.Link_Prof_center_Hie_cd)
ELSE BASE.Link_Prof_center_Hie_cd END)
from ecr_pims_stg.temp_units_like_fields Base

Error:

22:00:27 UTY8724 Select request submitted to the RDBMS.
**** 22:00:27 UTY8713 RDBMS failure, 3706: Syntax error: expected something between '(' and
the 'trim' keyword.
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 22:00:27 UTY6215 The restart log table has NOT been dropped.
**** 22:00:28 UTY6212 A successful disconnect was made from the RDBMS.

Thanks in advance!!!
3 REPLIES
Enthusiast

Re: Case Statement in Fast Export Utility Script

hi,
use the CHAR function instead of LENGTH.
LENGTH is teradata extension.

select
(CASE WHEN CHAR(trim(cast(BASE.Link_Prof_center_Hie_cd as CHAR(10))))= 4 THEN '000'||Trim(BASE.Link_Prof_center_Hie_cd)
ELSE BASE.Link_Prof_center_Hie_cd END)
from ecr_pims_stg.temp_units_like_fields Base

Hope this will work.
Senior Apprentice

Re: Case Statement in Fast Export Utility Script

LENGTH is ODBC-SQL syntax, it only works if the "Allow use of ODBC SQL extensions in queries" checkbox in Tools -> Options -> Query is checked.
It's always recommended to switch it off, because it will never work in CLI/JDBC/.NET/etc.

CHAR is a Teradata extension, better use CHAR_LENGTH, which is Standard SQL.

Dieter
Enthusiast

Re: Case Statement in Fast Export Utility Script

Thanks!! It really worked.

Kunal