Hive: Unsupported SubQuery Expression 'extract_date': Only SubQuery expressions that are top level conjuncts are allowed

Question

I am kinda new to HIVE and I am currently using HIVE 3.1.2 on WSL2 and I am trying to run this SQL script on HIVE CLI using the command hive -f test2subquery.sql:

use mydb;

SELECT
    date_photo as extract_date, camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa 
  WHERE
        extract_date = (select  CAST(y.extract_date as date) from app_params y)
  AND extract_date>= datein 
  AND extract_date< date_add(datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND statres <> 'X'
  AND id_stay_to = 'NULL'  -- eliminer les TO
  AND idstay <> idstay_res -- seulement les residents reel
  AND client_id_grp <> -1
  GROUP BY
   extract_date , camping_code

  )e
GROUP BY
date_photo, camping_code 

But I keep getting this error: Unsupported SubQuery Expression 'extract_date': Only SubQuery expressions that are top level conjuncts are allowed I tried using alias inside the subquery but i still get the same error.

use mydb;

SELECT
    s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        r.extract_date, r.camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa r
  WHERE
        r.extract_date = (select  CAST(y.extract_date as date) from app_params y)
  AND r.extract_date>= r.datein 
  AND r.extract_date< date_add(r.datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND r.statres <> 'X'
  AND r.id_stay_to = 'NULL'  -- eliminer les TO
  AND r.idstay <> r.idstay_res -- seulement les residents reel
  AND r.client_id_grp <> -1
  GROUP BY
   r.extract_date , r.camping_code

  )s
GROUP BY
date_photo, camping_code 

what also confuses me is when commenting the line extract_date< date_add(datein, nb_nuit_fid) like this:

use mydb;

SELECT
    s.date_photo as extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
            extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
  FROM
            extract_resa 
  WHERE
           extract_date <   (select cast(y.extract_date as date) from app_params y) 
  AND 
  extract_date >= datein 
  /* AND extract_date < date_add(datein, nb_nuit_fid) */
  AND nb_nuit_fid > 0
  AND statres <> 'X'
  AND id_stay_to = 'NULL'  -- eliminer les TO
  AND idstay <> idstay_res -- seulement les residents reel
  AND client_id_grp <> -1
  GROUP BY
     extract_date, camping_code
  )s
GROUP BY
date_photo, camping_code 

the script runs smoothly. please help.

Answer 1

convert your sub query clause to inner join.

use mydb;

SELECT
    s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        r.extract_date, r.camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa r
  INNER JOIN (select  CAST(y.extract_date as date) ext_dt from app_params y) y ON  r.extract_date = y.ext_dt -- this inner join will work same as your filter in subquery
  WHERE 
      r.extract_date>= r.datein 
  AND r.extract_date< date_add(r.datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND r.statres <> 'X'
  AND r.id_stay_to = 'NULL'  -- eliminer les TO
  AND r.idstay <> r.idstay_res -- seulement les residents reel
  AND r.client_id_grp <> -1
  GROUP BY
   r.extract_date , r.camping_code

  )s
GROUP BY
date_photo, camping_code 

I am not sure how commenting that line is working. Pls let me know the outcome.