Issue with subquery and select Top 1 statement in it

GaneshR

Vertica doesn't seem to support a statement like the one below:

        update upatable set Fcst_QTR=(select top 1 qtr_fcst from   fivec_qtr a where
            a.country=upatable.country and
            a.GBU=upatable.GBU and
            a.Occurence=upatable.Occurence and
            a.Program=upatable.Program)

 

I have tried using LIMIT but it is also not allowed by Vertica in a subquery. Any thoughts, ideas and help is welcome! Thank  you!

eli_revach

Hi

 

Try the below method :

 

update upatable set Fcst_QTR=(select qtr_fcst from (
select qtr_fcst,row_number() over () as rn from fivec_qtr a where
a.country=upatable.country and
a.GBU=upatable.GBU and
a.Occurence=upatable.Occurence and
a.Program=upatable.Program
) as aa where rn=1)

 

Thanks 

GaneshR

Thank you Eli!