Options

Issue with subquery and select Top 1 statement in it

GaneshRGaneshR
in General Discussion

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

  • Options
    eli_revacheli_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 

  • Options
    GaneshRGaneshR

    Thank you Eli!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...