SQL: LAG with variable parameter

Question

I want to use the lag function and make it depend on a variable, but it gives me an error because it must be of type integer.

I tried lag( pivote, pivote ) but I get the following error: AnalysisException: The offset parameter of LEAD/LAG must be a constant positive integer: lag(pivote, pivote)

Do you have any alternative?

The code is the following:

select 
    *
    ,if ( Monto > 0  
     , 0
     , lag( pivote, pivote ) OVER( partition by ID order by Fecha ) 
    ) as B
    
FROM(
    select 
        *, 
    row_number() OVER( partition by ID order by Fecha ) as pivote 
    FROM table1
    ) as base
;

@a_horse_with_no_name - user mentioned the dbms - its cloudera impala. — Koushik Roy, Feb 26 '22 at 17:50

score 0 · Answer 1 · answered Feb 26 '22 at 17:53

i think you probably mentioned pivote twice in lag(). can you pls try below SQL -

select 
    base.*
    ,if ( Monto > 0  
     , 0
     , lag( base.pivote ) OVER( partition by ID order by Fecha ) 
    ) as B
    
FROM(
    select 
        t.*, 
    row_number() OVER( partition by ID order by Fecha ) as pivote 
    FROM table1 t
    ) as base

SQL: LAG with variable parameter

1 Answers1