Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Looping without cursor not updating properly Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 10:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
ChrisM@Work (3/12/2013)
This solution I belive matches your description:


I tried working with this code and it parses fine but when I try running it, it throws this series of errors:

Msg 207, Level 16, State 1, Procedure anrsp_AwardFirewoodLotsSecondMethod, Line 45
Invalid column name 'rn'.
Msg 207, Level 16, State 1, Procedure anrsp_AwardFirewoodLotsSecondMethod, Line 62
Invalid column name 'rn'.
Msg 207, Level 16, State 1, Procedure anrsp_AwardFirewoodLotsSecondMethod, Line 84
Invalid column name 'rn'.

I thought maybe I'd mucked something up, so I tried copying and pasting it again, and it's still munging on me. I can see where rn is being set, and I can't figure out why the other bits of code can't see it.


ChrisM@Work (3/12/2013)
IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE #FirstChoice;
IF OBJECT_ID('tempdb..#SecondChoice') IS NOT NULL DROP TABLE #SecondChoice;
IF OBJECT_ID('tempdb..#RemainingPitches') IS NOT NULL DROP TABLE #RemainingPitches;
IF OBJECT_ID('tempdb..#RemainingPunters') IS NOT NULL DROP TABLE #RemainingPunters;
IF OBJECT_ID('tempdb..#TheChosenOnes') IS NOT NULL DROP TABLE #TheChosenOnes

-- Fill a small pot from a big table of hopefuls
SELECT TOP(80) -- reduced size of pot to fit sample data!!!
rn = ROW_NUMBER() OVER (ORDER BY NEWID()),
FirewoodLottery_id,
Choice1,
Choice2
INTO #TheChosenOnes
FROM [survey].[FirewoodLottery]
ORDER BY NEWID()


-- Allocate as many first choices as possible
SELECT
Choice = 1,
t.FirewoodTowns_id,
t.town,
t.Permits,
PermitsLeft = t.Permits - COUNT(*) OVER(PARTITION BY t.town),
x.FirewoodLottery_id
INTO #FirstChoice
FROM survey.FirewoodTowns t
CROSS APPLY ( -- randomly pick folks for available pitches
SELECT TOP(t.Permits)
l.FirewoodLottery_id
FROM #TheChosenOnes l
WHERE l.Choice1 = t.town -- first choice
ORDER BY rn
) x
ORDER BY t.town;

-- What pitches remain after first allocation?
SELECT DISTINCT
t.FirewoodTowns_id,
t.town,
t.Permits,
PermitsLeft = ISNULL(PermitsLeft,t.Permits)
INTO #RemainingPitches
FROM survey.FirewoodTowns t
LEFT JOIN #FirstChoice f ON f.FirewoodTowns_id = t.FirewoodTowns_id
WHERE ISNULL(PermitsLeft,t.Permits) > 0

-- What remains of the chosen ones after the first allocation?
SELECT
l.rn,
l.FirewoodLottery_id,
l.Choice2
INTO #RemainingPunters
FROM #TheChosenOnes l
WHERE NOT EXISTS (SELECT 1 FROM #FirstChoice f WHERE f.FirewoodLottery_id = l.FirewoodLottery_id)

-- Allocate any remaining pitches as second choices
SELECT
Choice = 2,
t.FirewoodTowns_id,
t.town,
Permits = t.Permits,
PermitsLeft = t.PermitsLeft - COUNT(*) OVER(PARTITION BY t.town),
x.FirewoodLottery_id
INTO #SecondChoice
FROM #RemainingPitches t
CROSS APPLY ( -- randomly pick from remaining punters
SELECT TOP(t.PermitsLeft)
l.FirewoodLottery_id
FROM #RemainingPunters l
WHERE l.Choice2 = t.town -- second choice
ORDER BY rn
) x
ORDER BY t.town;

-- Results
SELECT *
FROM #FirstChoice
UNION ALL
SELECT *
FROM #SecondChoice
ORDER BY
FirewoodTowns_ID,
Choice,
FirewoodLottery_id;

Post #1429945
Posted Tuesday, March 12, 2013 10:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
Curiouser and curiouser, it runs on my production system, but no on my dev system. But I just imported the data from prod to dev a short bit ago.
Post #1429947
Posted Tuesday, March 12, 2013 10:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 22,491, Visits: 30,183
Would it be a dumb question to ask what version of SQL Server you are running?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1429950
Posted Tuesday, March 12, 2013 10:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 1,651, Visits: 5,201
Siobhan Perricone (3/12/2013)
Curiouser and curiouser, it runs on my production system, but no on my dev system. But I just imported the data from prod to dev a short bit ago.


are both set at the same compatibility level? on the same version?


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1429951
    Posted Tuesday, March 12, 2013 10:36 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Tuesday, March 12, 2013 11:58 AM
    Points: 14, Visits: 20
    As far as I know, they're supposed to be. I mean, we told the server folks who set things up that they needed to be exactly the same.

    However, when I open up prod in management studio it says SQL Server 9.0.5069 and dev says SQL Server 9.0.4266

    Post #1429953
    Posted Tuesday, March 12, 2013 10:38 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 8:01 AM
    Points: 1,651, Visits: 5,201
    Siobhan Perricone (3/12/2013)
    As far as I know, they're supposed to be. I mean, we told the server folks who set things up that they needed to be exactly the same.

    However, when I open up prod in management studio it says SQL Server 9.0.5069 and dev says SQL Server 9.0.4266



    And the compatibility level on the databases themselves?


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1429956
    Posted Tuesday, March 12, 2013 10:45 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Tuesday, March 12, 2013 11:58 AM
    Points: 14, Visits: 20
    mister.magoo (3/12/2013)
    Siobhan Perricone (3/12/2013)
    As far as I know, they're supposed to be. I mean, we told the server folks who set things up that they needed to be exactly the same.

    However, when I open up prod in management studio it says SQL Server 9.0.5069 and dev says SQL Server 9.0.4266

    And the compatibility level on the databases themselves?


    Both say they are SQL Server 2005 (90)
    Post #1429966
    Posted Tuesday, March 12, 2013 10:48 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 8:01 AM
    Points: 1,651, Visits: 5,201
    Drop #TheChosenOnes on it's own first and then run the whole thing.

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1429971
    Posted Tuesday, March 12, 2013 11:30 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Tuesday, March 12, 2013 11:58 AM
    Points: 14, Visits: 20
    mister.magoo (3/12/2013)
    Drop #TheChosenOnes on it's own first and then run the whole thing.


    Ok, coworker managed to get it to work now it works for me. He didn't do any changes to it. I pasted the code into an email, he copied it out of the email and managed to alter the stored procedure with the code. I'm not going to try and figure this out any more and I'm just going with what I got!

    Thanks again, everyone, for your time and expertise. You've all been massively patient with me and I appreciate that as much as I appreciate you working this out for me.
    Post #1430000
    « Prev Topic | Next Topic »

    Add to briefcase «««1234

    Permissions Expand / Collapse