Teradata conversion to hive sql using case when

Question

I am trying to convert the below teradata sql to hive sql but I get empty table.

TERADATA SQL:

 SELECT              
 TRIM(CAST(BOOK_ID AS BIGINT)) || '_' ||TRIM(CAST(REF_ID AS BIGINT)) 
 AS BOOK_REF,
 CASE WHEN (PHOTO_COUNT > 0) AND (INDEX(PICTURE_URL , ';')>0) THEN 
 SUBSTRING(PICTURE_URL FROM 1 FOR POSITION(';' IN PICTURE_URL)-1)
 ELSE PICTURE_URL
 END AS MAIN_IMAGE 
                     
 FROM GENERIC_BOOKS;

HIVE :

 SELECT 
 CASE WHEN (PHOTO_COUNT > 0) AND (instr(A.PICTURE_URL, ';') > 0) THEN 
 SUBSTRING(A.PICTURE_URL, 1, FIND_IN_SET(';', A.PICTURE_URL))-1
    ELSE A.PICTURE_URL
    END AS ITEM_MAIN_IMAGE
 FROM GENERIC_BOOKS;

PICTURE_URL for example : https://booking.com/00/s/OTAwWDE2A=/z/wKEAAOSwfURc~gng/$_57.JPG?set_id=8800005007;https://booking.com/00/s/OTAwW2MDA=/z/LQcAAOSwrzxc~gni/$_57.JPG?set_id=8800005007;https://booking.com/00/s/OTAwW2MDA=/z/XAIAAOSw7J1c~gnl/$_57.JPG?set_id=8800005007;https://booking.com/00/s/OTAwW2MDA=/z/aA8AAOSwYT1c~gnv/$_57.JPG?set_id=8800005007

For this example, the expected MAIN_IMAGE should be: https://booking.com/00/s/OTAwWDE2A=/z/wKEAAOSwfURc~gng/$_57.JPG?set_id=8800005007

Answer 1

Hive SQL doesn't support Standard SQL POSITION and Teradata's INDEX (don't know why both are used, different syntax to get the same result).

Both can be replaced by LOCATE.

Addionally Standard SQL SUBSTRING is also not implemented in Hive, it's SUBSTR:

CASE WHEN (PHOTO_COUNT >0) AND (locate(';', PICTURE_URL)>0) THEN 
 SUBSTR(PICTURE_URL, 1, locate(';', PICTURE_URL)-1)