CODEWITHSUNDEEP
sqlvariablesimpalalaglead
Angel Guadarrama
Angel Guadarrama

Reputation: 3

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
;

Upvotes: 0

Views: 574

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7407

I think you probably mentioned pivote twice in lag(). can you please 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

Upvotes: 0

Related Questions