All Forums Database
SHERIN JEYABOSE 18 posts Joined 11/14
17 Dec 2014
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 ?
Rohan_Sawant 55 posts Joined 07/14
17 Dec 2014

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.

 

 

 

 

SHERIN JEYABOSE 18 posts Joined 11/14
17 Dec 2014

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 ?

dnoeth 4628 posts Joined 11/04
17 Dec 2014

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))

 

Dieter

SHERIN JEYABOSE 18 posts Joined 11/14
19 Dec 2014

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

SHERIN JEYABOSE 18 posts Joined 11/14
19 Dec 2014

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..!!!

SHERIN JEYABOSE 18 posts Joined 11/14
19 Dec 2014

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 ?

Rohan_Sawant 55 posts Joined 07/14
19 Dec 2014

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-exception

Thanks,

Rohan Sawant

SHERIN JEYABOSE 18 posts Joined 11/14
22 Dec 2014

Thanks Rohan..it is informative.

SHERIN JEYABOSE 18 posts Joined 11/14
19 Jan 2015

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 ?

You must sign in to leave a comment.