Impala SQL Query

Question

Error Message :

select list expression not produced by aggregation output (missing from GROUP BY clause?): CASE WHEN (flag = 1) THEN date_add(lead_ctxdt, -1) ELSE ctx_date END lot_endt

code :

select c.enrolid, c.ctx_date, c.ctx_regimen, c.lead_ctx, c.lead_ctxdt, min(c.ctx_date) as lot_stdt, 
case when (flag = 1 ) then date_add(lead_ctxdt, -1) 
else ctx_date
end as lot_endt
from
(
    select p.*, 
    case when (ctx_regimen <> lead_ctx) then 1 
    else 0
    end as flag
    from
    (
        select a.*, lead(a.ctx_regimen, 1) over(partition by enrolid order by ctx_date) as lead_ctx, 
        lead(ctx_date, 1) over (partition by enrolid order by ctx_date) as lead_ctxdt
        from 
        (
            select enrolid, ctx_date, group_concat(distinct ctx_codes) as ctx_regimen
            from lotinfo 
            where ctx_date between ctx_date and date_add(ctx_date, 5)
            group by enrolid, ctx_date
        ) as a
   ) as p
) as c
group by c.enrolid, c.ctx_date, c.ctx_regimen, c.lead_ctx, c.lead_ctxdt

I want to get the lead_ctx date minus one as the date when the flag is 1

Depends on what you want, try applying an aggregate function: MIN/MAX(case when (flag = 1 ) then date_add(lead_ctxdt, -1) else ctx_date end) — dnoeth
@dnoeth; I want to get the lead_ctx date minus one as the date when the flag is 1. I think you do have an answer here. Probably, there is only one row per group with the flag enabled anyway. — GMB
This looks like an interesting query, probably related to a gaps-and-island problem. If you were to provide sample data and desired results as tabular text, one might be able to further optimize it. — GMB

Saipoojitha Chander Saipoojitha Chander · Accepted Answer · 2020-09-02T10:38:51

So i found the answer by executing a couple of times the minor changes. Let me tell you, that when you are trying to min or max alongside you have group_conact in the same query then in Impala this doesn't work. You have to write it in two queries per one more sub query and the min() of something in the outer query or vice versa.

Thank you @dnoeth for letting me understand I have the answer with me already.

Impala SQL Query

1 Answers