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并不支持SlectT条款中的子类。你们需要重述逻辑:

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 ;

Answer 4

我对近乎选择的发言视而不见。但后来又认识到,我的哪里条款是错误的。我这样做了。

select * from (select * from x where a = "something" AND 
AND b = "something else") tmp

点为

DON T LOOK NEAR SlectT STATEMENT BUT CHECK YOUR questRY

,看看是否使用了某些未经证实的职能。

DON T LOOK NEAR SlectT STATEMENT BUT CHECK YOUR questRY

友情链接