Looping without cursor not updating properly

• Siobhan Perricone

  SSC Journeyman

  Points: 98

  More actions

  March 12, 2013 at 10:27 am

  #1596130

  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;

• Siobhan Perricone

  SSC Journeyman

  Points: 98

  More actions

  March 12, 2013 at 10:30 am

  #1596131

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 12, 2013 at 10:32 am

  #1596133

  Would it be a dumb question to ask what version of SQL Server you are running?

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  March 12, 2013 at 10:32 am

  #1596134

  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
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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