Search code examples
hivecastingcaseteradata

Convert query Teradata to Hive

i have the following query and it executes successfully in teradata.

CASE 
WHEN Cast(STG_101_118_INVM.ACCT_OPEN_DT AS INTEGER) > 2956565 THEN Cast('2999-12-31'AS DATE) 
WHEN Cast(STG_101_118_INVM.ACCT_OPEN_DT AS INTEGER) = 0 THEN Cast('1900-01-01' AS DATE) 
ELSE Cast('1900-01-01' AS DATE) + Cast(STG_101_118_INVM.ACCT_OPEN_DT AS INTEGER) - 1 END 
AS ACCT_OPEN_DT

i have the following query and it executes successfully in teradata. but when run on hive it doesn't work and it shows a warning like this.

Error while compiling statement: FAILED: SemanticException line 
0:undefined:-1 Wrong arguments 'ACCT_OPEN_DT': No matching method for 
class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPDTIPlus with 
(date, int)

what should i change? can you help me to convert this query to hive format?

Solution

  • You have to use date_add() to add ACCT_OPEN_DT INT to a date. You can use below SQL -

    CASE 
WHEN Cast(STG_101_118_INVM.ACCT_OPEN_DT AS INTEGER) > 2956565 THEN Cast('2999-12-31'AS DATE) 
WHEN Cast(STG_101_118_INVM.ACCT_OPEN_DT AS INTEGER) = 0 THEN Cast('1900-01-01' AS DATE) 
ELSE DATE_ADD(Cast('1900-01-01' AS DATE) ,Cast(STG_101_118_INVM.ACCT_OPEN_DT AS INTEGER) - 1) END 
AS ACCT_OPEN_DT