1

I am working on migration project of Oracle to Teradata.

The tables have been migrated using datastage jobs.

How do I migrate Oracle Views to Teradata?

Direct script copying is not working due to SQL statements difference of both databases

Please help?

Ian Carpenter
  • 7,924
  • 5
  • 45
  • 71
user1379055
  • 11
  • 3
  • 1
    Which part(s) of the view in the Oracle view are you having issues identifying a Teradata equivalent? Can you share any part of the view definition? Many functions native to Oracle that are not ANSI standard will require a UDF in Teradata of which some may be downloaded from http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions – Rob Paller May 15 '12 at 16:12
  • DECODE (t.satisfaction, NULL, 'Not Evaluated', t.satisfaction) satisfaction, ................... error is it says satisfaction is not a data type :( – user1379055 May 16 '12 at 05:59

1 Answers1

2

The DECODE() Oracle function is available as part of the Oracle UDF Library on the Teradata Developer Exchange Downloads section. Otherwise, you are using the DECODE function in your example in the same manner in which the ANSI COALESCE() function behaves:

COALESCE(t.satisfaction, 'Not Evaluated')

It should be noted that the data types of the COALESCE() function must be implicitly compatible or you will receive an error. Therefore, t.satisfaction would need to be at least CHAR(13) or VARCHAR(13) in order for the COALESCE() to evaluate. If it is not, you can explicitly cast the operand(s).

COALESCE(CAST(t.satisfaction AS VARCHAR(13)), 'Not Evaluated')

If your use of DECODE() includes more evaluations than what is in your example I would suggest implementing the UDF or replacing it with a more standard evaluated CASE statement. That being said, with Teradata 14 (or 14.1) you will find that many of the Oracle functions that are missing from Teradata will be made available as standard functions to help ease the migration path from Oracle to Teradata.

Rob Paller
  • 7,506
  • 23
  • 23