'SQL: LAG with variable parameter

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
;
sqlvariablesimpalalaglead


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Koushik Roy

Related Questions