Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Angry Unanswered: An ugly self-join (oh, that doesn't work!!)

    Hi again, all...here I am again, trying to work on my CloseIndex thang again...Same subject, different tack...

    Basically, I have two tables...for the sake of simplicity, let me define them as:

    Code:
    PortfolioIndex
      PortfolioID int
      CreateDate smalldatetime
      CloseIndex float
    
    PortfolioPerformance
      PortfolioID int
      CreateDate smalldatetime
      PrevDate smalldatetime
      DailyPerChg float
    
    UPDATE    PortfolioIndex
    SET          CloseIndex = CASE 
                                           WHEN PPI.CloseIndex IS NULL THEN 100.00
                                           ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
                                           END
    FROM PortfolioIndex AS P INNER JOIN PortfolioIndex AS PPI on (P.PortfolioID = PPI.PortfolioID), PortfolioPerformance AS PP
    WHERE  (P.PortfolioID = PP.PortfolioID) AND
    	((P.CreateDate = PP.CreateDate) AND
    	(P.CreateDate = @CreateDate) AND
    	(PPI.CreateDate = PP.PrevDate))
    What I am trying to do is...get the previous day's portfolioIndex row's CloseIndex and create a new one for today's row.

    As ugly as it is, it works when I execute it in the SQL Query Analyzer, but when I try to create the stored procedure, the syntax check complains that the PortfolioIndex reference at the UPDATE... part is AMBIGUOUS...yet when I define it in the SP as P.PortfolioIndex, it fails at run time saying there is no object named P.PortfolioIndex (well, of course there isn't!).

    How can I make this work (and if possible, make it prettier too! *L* )
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    well, now I see it doesn't really work in the sql analyzer either...but did on a previous iteration (before I added the inner join).

    Still, the idea/question is the same...is there a less kludg-ey way to do the self-join to get the previous day's row and update the new row based on data from the old and the 2nd (PortfolioPerformance) table?

    Thanks,
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    I THOUGHT it usta work...

    Code:
    select /*PI.CloseIndex = */ PI.PortfolioID, CASE 
      		 WHEN PPI.CloseIndex IS NULL THEN 100.00
      		 ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
      		 END
    from PortfolioIndex PI, PortfolioIndex PPI, PortfolioPerformance PP
    where ((PI.PortfolioID = PPI.PortfolioID AND
    	PI.PortfolioID = PP.PortfolioID))AND
    	((PI.CreateDate = PP.CreateDate) AND
    	(PI.CreateDate = @CreateDate) AND
    	(PPI.CreateDate = PP.PrevDate))
    It worked this way in the SQL Analyzer...when I was testing without the update...so...how can I reference the table in an update without being ambiguous? I can't seem to find any example code anywhere about updates with self-joins...can it be done?

    Thanks,
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Update table
    set column = (select ...)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    *blush*

    Thanks...*hanging head*

    It must be time to go home...

    Thanks for taking the time to point me in the direction of the forest once again...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    ate crow too quickly?

    I've been trying to get the select correct as suggested, but can't get it to work for me.

    Possibly because one requirement is not plain from my previous description...

    There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).

    Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*

    Here is my modified query:
    Code:
    DECLARE @CreateDate smalldatetime
    DECLARE @PrevDate smalldatetime
    SET @CreateDate = '2004-02-13'
    SET @PrevDate = '2004-02-12'
    
    Update PortfolioIndex
    Set CloseIndex = (
    select CASE 
      		 WHEN PPI.CloseIndex IS NULL THEN 100.00
      		 ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
      		 END
    from  PortfolioIndex PPI, PortfolioPerformance PP
    where (PPI.PortfolioID = PP.PortfolioID)AND
    (PP.CreateDate = @CreateDate) AND
          (PPI.CreateDate = @PrevDate))
    The result is:
    Server: Msg 512, Level 16, State 1, Line 6
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    Any helpful suggestions?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Re: ate crow too quickly?

    anyone? I know I must be missing something obvious, but can't see it...
    how does one update a table joined to itself in the select clause???

    Originally posted by TallCowboy0614
    I've been trying to get the select correct as suggested, but can't get it to work for me.

    Possibly because one requirement is not plain from my previous description...

    There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).

    Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*

    Here is my modified query:
    Code:
    DECLARE @CreateDate smalldatetime
    DECLARE @PrevDate smalldatetime
    SET @CreateDate = '2004-02-13'
    SET @PrevDate = '2004-02-12'
    
    Update PortfolioIndex
    Set CloseIndex = (
    select CASE 
      		 WHEN PPI.CloseIndex IS NULL THEN 100.00
      		 ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
      		 END
    from  PortfolioIndex PPI, PortfolioPerformance PP
    where (PPI.PortfolioID = PP.PortfolioID)AND
    (PP.CreateDate = @CreateDate) AND
          (PPI.CreateDate = @PrevDate))
    The result is:
    Server: Msg 512, Level 16, State 1, Line 6
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    Any helpful suggestions?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The error say it all...how can you update a column with n results....you need to make sure the query only returns 1
    per row

    Forget the update and just focus on the select to make sure it's returning what you need.
    Last edited by Brett Kaiser; 02-18-04 at 14:29.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •