cannot recognize input near 'select' in subquery

Question

SELECT 
    (case TRIM(T.tactictype)
        WHEN  'Economics' THEN
            (select economic_tactic_id from cfext.economic_tactics E where LOWER(E.economic_tactic_name) = LOWER(T.tacticname) )
        WHEN  'Cyber' THEN
            (select cyber_tactic_id from cfext.cyber_tactics E where LOWER(E.cyber_tactic_name) = LOWER(T.tacticname) )
    end) AS tacticid 
FROM cfext.banking_crreport_allfiles T
LIMIT 50;

I am trying to run the above query, below error. Can anybody please help.

FAILED: ParseException line 4:1 cannot recognize input near 'select' 'economic_tactic_id' 'from' in expression specification

Answer 1

Try to use

SELECT (case TRIM(T.tactictype) WHEN 'Economics' THEN (select E.economic_tactic_id from cfext.economic_tactics E 
    where LOWER(E.economic_tactic_name) = LOWER(T.tacticname) ) 
WHEN 'Cyber' THEN (select E.cyber_tactic_id from cfext.cyber_tactics E where LOWER(E.cyber_tactic_name) = LOWER(T.tacticname) ) end) AS tacticid 
FROM cfext.banking_crreport_allfiles T LIMIT 50;

Answer 2

SELECT (case TRIM(T.tactictype)
     WHEN 'Economics' THEN
      (select et.economic_tactic_id
         from cfext.economic_tactics et
        where LOWER(et.economic_tactic_name) = LOWER(T.tacticname))
     WHEN 'Cyber' THEN
      (select e.cyber_tactic_id
         from cfext.cyber_tactics e
        where LOWER(e.cyber_tactic_name) = LOWER(T.tacticname))
   end) AS tacticid`enter code here` FROM cfext.banking_crreport_allfiles T LIMIT 50;

Answer 3

Hive does not support subqueries in the SELECT clause. You need to rephrase the logic:

SELECT COALESCE(E.economic_tactic_id, C.cyber_tactic_id) as tacticid 
FROM cfext.banking_crreport_allfiles T LEFT JOIN
     cfext.economic_tactics E 
     ON LOWER(E.economic_tactic_name) = LOWER(T.tacticname) AND
        TRIM(T.tactictype) = 'Economics' LEFT JOIN
     cfext.cyber_tactics C
     ON LOWER(C.cyber_tactic_name) = LOWER(T.tacticname) AND
        TRIM(T.tactictype) = 'Cyber';