Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Issue with subquery and select Top 1 statement in it

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!

Comments

  • 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 

  • Thank you Eli!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.