0

i ran the following query on teradata it was successful but when running on hive it got an error

Coalesce(CASE WHEN To_Number(STG_156_110_CUID.ID_ISSUE_DATE)  >= '29991231' OR 
To_Number(STG_156_110_CUID.ID_ISSUE_DATE)  <= '1901000' OR 
To_Number(STG_156_110_CUID.ID_ISSUE_DATE) IS NULL THEN Cast(NULL AS DATE Format 'YYYYMMDD') 
ELSE Cast(STG_156_110_CUID.ID_ISSUE_DATE AS DATE Format 'YYYYMMDD')
END, Cast ('19000101' AS DATE Format 'YYYYMMDD' )) AS ID_ISSUE_DATE

Error

Error while compiling statement: FAILED: SemanticException [Error 10011]: Invalid function 
To_Number

what should i replace to_number with?

ryan
  • 53
  • 6
  • 2
    Does this answer your question? [Hive: Convert String to Integer](https://stackoverflow.com/a/12358166/13927534) – Miss Skooter Mar 20 '23 at 09:04
  • 1
    Or if the field's a date: https://stackoverflow.com/a/50259888/361842 – JohnLBevan Mar 20 '23 at 09:05
  • 1
    there is no built-in To_Number function. Instead, you can use the cast function to convert a string to a numeric type. So in your case, you can replace the To_Number function with cast(STG_156_110_CUID.ID_ISSUE_DATE as int). This will cast the ID_ISSUE_DATE column to an integer type, which you can then compare to other integer values. Here's the updated query: – Joker Mar 20 '23 at 13:32
  • 1
    coalesce(case when cast(STG_156_110_CUID.ID_ISSUE_DATE as int) >= 29991231 or cast(STG_156_110_CUID.ID_ISSUE_DATE as int) <= 1901000 or STG_156_110_CUID.ID_ISSUE_DATE is null then cast(null as date format 'YYYYMMDD') else cast(STG_156_110_CUID.ID_ISSUE_DATE as date format 'YYYYMMDD') end, cast('19000101' as date format 'YYYYMMDD')) as ID_ISSUE_DATE – Joker Mar 20 '23 at 13:33
  • 1
    Please note that this assumes that the ID_ISSUE_DATE column is stored as a string in the format 'YYYYMMDD'. If it is stored in a different format, you may need to adjust the cast accordingly. – Joker Mar 20 '23 at 13:33
  • 1
    Just FYI - It is bad practice to convert one value to number and then compare with a string. Teradata will accept it (for historical reasons) and implicitly convert *both* values to FLOAT to do the comparison. Some other databases will just throw an error. – Fred Mar 20 '23 at 14:49

0 Answers0