SQL to TERADATA Migration

Database
Enthusiast

SQL to TERADATA Migration

  1. CONVERT (VARCHAR,COL1,23))AS VARCHAR(100) - here VARCHAR is used like an attribute in SQL whereas it is a datatype.  Will TD accept such ?
9 REPLIES
Junior Supporter

Re: SQL to TERADATA Migration

Hi Sherin,

Any TD keyword could be used as an attribute/columnname/variable only if the the variable is between " and ".

Example:

SELECT

'VALUE' AS "VARCHAR"

,CAST("VARCHAR" AS VARCHAR(100)); ---> Runs fine

Output:

      VARCHAR   VARCHAR

1      VALUE       VALUE


SELECT

'VALUE' AS VARCHAR

,CAST(VARCHAR AS VARCHAR(100));---> throws error

(SELECT Failed. 3707:  Syntax error, expected something like a name or a Unicode delimited identifier between the 'AS' keyword and the 'VARCHAR' keyword.)

Your query will not run since it uses "CONVERT" which is not a function of TD. You could use "CAST" and then use "FORMAT" to achieve the same.

Enthusiast

Re: SQL to TERADATA Migration

Question #2:-

SELECT

BORR.[CUSTOMER_ID]

,BORR.[ORIG_CUSTOMER_ID]

FROM TABLE1 BORR

is a MS SQL code..in which [...] tells even if i give [CUSTOMER(space)ID] or [ORIG(space)CUSTOMER(space)ID] with spaces inbetween would be considered as same as the attribute name....(space would not be considered)...

Do we have like this kind in TERADATA ?

Junior Contributor

Re: SQL to TERADATA Migration

You do the same you should do in MS SQL Server instead of the proprietary [] syntax, use double quotes:

"CUSTOMER ID"

You CONVERT example is not valid syntax in MS. But col1 seems to be a datetime,  depending on your TD release this is probably:

TO_CHAR(DATE, 'yyyy-mm-dd')
or
CAST(CAST(DATE AS FORMAT 'yyyy-mm-dd') AS CHAR(10))
Enthusiast

Re: SQL to TERADATA Migration

Thanks a lot RS..its a lesson for life...

Enthusiast

Re: SQL to TERADATA Migration

Wow...thank you Dieter..we will be using TD 14.10 for our migration...

being a TD lover n learner, I will surely encounter way many doubts....pls bear and clarify me sparing some time..!!!

Enthusiast

Re: SQL to TERADATA Migration

Question #3 :- RAISEERROR() in MS-SQL...is there any clever way of equivalent TD function instead of writing a big worked out UDF for this ?

Junior Supporter

Re: SQL to TERADATA Migration

Hi Sherin,

If you are talking about raising errors in TD objects as such for particular situation could be seen only in STORED PROCEDURES. We specially handle errors in STORED PROCEDURES by using EXCEPTION HANDLING techniques. Please refer forum 

https://forums.teradata.com/forum/database/handler-for-sqlexception-does-not-execute-on-sql-exceptio...

Thanks,

Rohan Sawant

Enthusiast

Re: SQL to TERADATA Migration

Thanks Rohan..it is informative.

Enthusiast

Re: SQL to TERADATA Migration

What is the best file format which supports fastload ? There are various...but would like to know the best file format when connected with LINUX - a fastload supports efficiently ?