FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression yyyy-MMM-dd: Only SubQuery expressions that are top level conjuncts are allowed

Question

I am trying to execute below query in Hive.

SELECT
       B.ALTACCT
     , C.DESCR
     , B.JRNL_LN_REF
     , B.LINE_DESCR
     , CASE
              WHEN B.MONETARY_AMOUNT >0
                     THEN B.MONETARY_AMOUNT
                     ELSE B.MONETARY_AMOUNT * (-1)
       END
     , CASE
              WHEN B.MONETARY_AMOUNT > 0 THEN '+1'
                     ELSE '-1'
       END
     , B.FOREIGN_AMOUNT
     , B.FOREIGN_CURRENCY
     , B.JOURNAL_LINE
FROM
       TABLE1 B
     , TABLE2 C
WHERE
       B.LEDGER      = 'ACT_LOC'
       AND C.ALTACCT = B.ALTACCT
       AND C.EFFDT   =
       (
              SELECT
                     MAX(C_ED.EFFDT)
              FROM
                     TABLE3 C_ED
              WHERE
                     C.SETID                                        = C_ED.SETID
                     AND C.ALTACCT                                  = C_ED.ALTACCT
                     AND UNIX_TIMESTAMP(C_ED.EFFDT, 'yyyy-MMM-dd') <= UNIX_TIMESTAMP('2021-JUL-08', 'yyyy-MMM-dd')
       )
       AND C.SETID = 'FRSHR'
       AND B.ALTACCT NOT IN ('1','2','3')
union
SELECT
       G.ALTACCT
     , H.DESCR
     , G.JRNL_LN_REF
     , G.LINE_DESCR
     , CASE
              WHEN G.MONETARY_AMOUNT >0
                     THEN G.MONETARY_AMOUNT
                     ELSE G.MONETARY_AMOUNT *(-1)
       END
     , CASE
              WHEN G.MONETARY_AMOUNT >0 THEN '+1'
                     ELSE '-1'
       END
     , ' '
     , ' '
     , G.FOREIGN_AMOUNT
     , G.FOREIGN_CURRENCY
     , G.JOURNAL_LINE
FROM
       TABLE1 G
     , TABLE2 H
WHERE
       H.ALTACCT   = G.ALTACCT
       AND H.EFFDT =
       (
              SELECT
                     MAX(H_ED.EFFDT)
              FROM
                     TABLE3 H_ED
              WHERE
                     H.SETID                                        = H_ED.SETID
                     AND H.ALTACCT                                  = H_ED.ALTACCT
                     AND UNIX_TIMESTAMP(H_ED.EFFDT, 'yyyy-MMM-dd') <= UNIX_TIMESTAMP('2021-JUL-08', 'yyyy-MMM-dd')
       )
       AND H.SETID            = 'FRSHR'
       AND G.LEDGER           = 'ACT_LOC'
       AND G.MONETARY_AMOUNT <> 0
       AND G.ALTACCT NOT IN ('1','2','3')

Getting error as

Error while compiling statement: FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression yyyy-MMM-dd: Only SubQuery expressions that are top level conjuncts are allowed

Can someone please help?

Answer 1

Rewrite your query using joins:

...
FROM TABLE1 B 
     INNER JOIN TABLE2 C ON C.ALTACCT = B.ALTACCT AND C.SETID = 'FRSHR'
     INNER JOIN (
                 SELECT C_ED.SETID, C_ED.ALTACCT
                        MAX(C_ED.EFFDT) MAX_EFFDT
                   FROM TABLE3 C_ED
                  WHERE UNIX_TIMESTAMP(C_ED.EFFDT, 'yyyy-MMM-dd') <= UNIX_TIMESTAMP('2021-JUL-08', 'yyyy-MMM-dd')
               GROUP BY C_ED.SETID, C_ED.ALTACCT
               ) C_ED ON C.SETID = C_ED.SETID 
                     AND C.ALTACCT = C_ED.ALTACCT
                     AND C.EFFDT = C_ED.MAX_EFFDT

WHERE B.LEDGER = 'ACT_LOC'
  AND B.ALTACCT NOT IN ('1','2','3')

Do the same for second subquery in UNION.