Impala Error : AnalysisException: Multiple subqueries are not supported in expression: (CASE *** WHEN THEN)

Question

I have a where Clause that I need to check if values exists in a table, and I'm doing that in a (subquery). The problem is, that should be made based on values - 'FIX' and 'VAR'. Depending on each, we need to check on a different table (subquery). To achieve that goal I'm using a Case When statement in the where clause, as shown below:

select *

FROM T1

where   
        
        (upper(trim(ITAXAVAR)) = 'S'        

        and
            (
             upper(trim(CTIPAMOR)) not in ('A','U','F')
            )
        )

        and
        
        --problem starts here.....
        (case ucase(trim(CTIPTXFX))     --Values 'FIX';'VAR';'PUR'

            WHEN 'FIX'      
                THEN
                    (concat(trim(CPRZTXFX),trim(CTAXAREF)) not in
                     
                        (select trim(A.tayd91c0_celemtab)
                            from cd_estruturais.tat91_tabelas A
                            where   A.tayd91c0_ctabela = 'W03' and  
                                    --data_date_part = '${Data_ref}' and        --por vezes não temos actualização TAT91 para mesma data_ref das tabelas
                                    A.data_date_part = (select max(B.data_date_part)
                                                        from cd_estruturais.tat91_tabelas B
                                                        where A.tayd91c0_ctabela = B.tayd91c0_ctabela and 
                                                              B.data_date_part > date_add(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())),-5)
                                                        )       
                                    
                                    and length(nvl(trim(A.tayd91c0_celemtab),'')) <> 0        
                        )        
                    )
                    
            WHEN 'VAR'     
                THEN
                (concat(trim(CTAXAREF),trim(CPERRVTX)) not in
                 
                            (select concat(trim(A.CTXREF),trim(A.CPERRVTX))
                             from land_estruturais.cat01_taxref A
                             where A.data_date_part > date_add(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())),-5)
        
                                      and length(nvl(concat(trim(A.CTXREF),trim(A.CPERRVTX)),'')) <> 0        
                            )        
                )
            
        END
        )        
;   

Below is a simplified view of the same query:

select *

FROM T1

where   
        
        (--first criteria
        )

        and
        
        --problem starts here.....
        (case ucase(trim(CTIPTXFX))     --Values 'FIX';'VAR';'PUR'

            WHEN 'FIX'      
                THEN
                    (field1 not in
                        (subquery 1)       
                    )
                    
            WHEN 'VAR'     
                THEN
                    (field1 not in
                        (subquery 2)
            
        END
        )        
;   

Can anyone tell me what I'm doing wrong, please? I seems to me that Impala does not support the subqueries inside a Case When Statement.

Thank you.

Answer 1

Impala doesnt support Subqueries in the select list. So, you need to rewrite the SQL like below -

1. Use LEFT ANTI JOIN in place of NOT IN() to link subqueries to T1.
2. To handle case when, use UNION ALL for different conditions.

SELECT * FROM T1
  LEFT ANTI JOIN subqry1 y ON T1.id = y.id
  WHERE col='FIX' 
UNION ALL
SELECT * FROM T1
  LEFT ANTI JOIN subqry2 y ON T1.id = y.id
  WHERE col='VAR' 

I tried to change the simple SQL you posted above. The main SQL is too complex and need table setup and data to prove the logic. Here is my version of your simple SQL -

select * FROM T1
LEFT ANTI JOIN subquery1 ON subquery1.column = T1.field1
where   (--first criteria )
        and ucase(trim(CTIPTXFX))='FIX'
UNION ALL
select * FROM T1
LEFT ANTI JOIN subquery2 ON subquery2.column = T1.field1
where   (--first criteria )
        and ucase(trim(CTIPTXFX))='VAR'  

Pls note, Anti join and union all can be expensive so if your table size if huge, please tune them accordingly.