Unsupported Subquery Expression:Correlating Expression Cannot Contain Unqualified Column References

Unsupported SubQuery Expression : Correlating expression cannot contain unqualified column references

You can find someone having the same problem here and the corresponding JIRA ticket.

Based on those answers, I would advise you to try :

Select a,b,c from t1 where a in (0,100) AND t1.b in (select b from t2
  where d>2 GROUP BY 1 ) LIMIT 1;

Unsupported SubQuery Expression ''Fashion'': SubQuery expression refers to Outer query expressions only

Use analytic functions to calculate fashion_flag per UserId:

select UserId, Category
from
( --calculate User level flags
select UserId, Category,
       max(fashion_flag)     over (partition by UserId) as user_fashion_flag,
       max(electronics_flag) over (partition by UserId) as user_electronics_flag
from
(--maybe you do not need this subquery, if case will work inside max() over
    select UserId, Category,
           case when Category='Fashion'     then 1 else 0 end fashion_flag,
           case when Category='Electronics' then 1 else 0 end electronics_flag
      from customer_data 
     where (Category in ('Fashion','Electronics'))
       and (Action in ('Click','AddToCart','Purchase'))
) s

) s
where user_fashion_flag+user_electronics_flag=1 --not allow two flags at a time
;

Hadoop - Hive sub-queries - Not In Clause

I don't have you exact data so it is hard to verify this but I would do something like

SELECT COUNT(*)
FROM (
  SELECT a.*
    , flg
  FROM mydata a
  LEFT OUTER JOIN (
    SELECT store_out, flg
    FROM (
      SELECT store_out
        , cnt
        , 1 AS flg
        , AVG(cnt)         OVER () AS avg_cnt
        , STDDEV_SAMP(cnt) OVER () AS std_cnt
      FROM (
        SELECT store AS store_out
          , COUNT(*) AS cnt
        FROM mydata
        GROUP BY store ) x
      ) y
    WHERE cnt > avt_cnt + std_cnt AND cnt < avg_cnt - std_cnt ) z
  ON a.store = z.store_out ) final
WHERE flg IS NULL

Basically, left join the subquery and create a dummy column. That column won't exists in the main table so where all the flg values are NULL, these are the stores you want. Hope this helps.

SQL Query, Multiple Selections

You could use a subquery of some sorts.

select ID, Category
from Table1
where ID in (select ID from Table1 where Category = 241)

---

Related Topics