November 15, 2010 at 9:05 am
I am getting error msg's with my syntax. . Can you help me out ?
Declare @StoreNum int
Declare @StoreCnt int
Declare @Recid int
Set @Recid = Select MAX(RECNO) as recno from dbo.as_AllStores
GO
Drop Table dbo.as_Redemption_Offers_DayGap
GO
Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.*
Into dbo.as_Redemption_Offers_DayGap
FROM dbo.as_offer_dates as ao
Inner Join (Select *
From dbo.as_all_division_coupons
Where @Recid = RECNO ) AS ar1
ON ao.[date] - 1 = ar1.SCAN_DATE_CONVERT AND
ao.DivNum = ar1.Kma_div_nbr AND
ao.cpnnum = ar1.coupon_nbr
Inner Join (Select *
From dbo.as_all_division_coupons
Where @Recid = RECNO ) AS ar2
ON ao.[date] + 1 = ar2.SCAN_DATE_CONVERT AND
ao.DivNum = ar2.Kma_div_nbr AND
ao.cpnnum = ar2.coupon_nbr
Left Join (Select *
From dbo.as_all_division_coupons
Where @Recid = RECNO ) As ar3
ON ao.[date] = ar3.SCAN_DATE_CONVERT AND
ao.DivNum = ar3.Kma_div_nbr AND
ao.cpnnum = ar3.coupon_nbr
WHERE ar3.STORE IS NULL
GO
Set @Recid = @Recid - 1
GO
While @Recid > 0
begin
Insert Into dbo.as_Redemption_Offers_DayGap
(Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.*
FROM dbo.as_offer_dates as ao
Inner Join (Select *
From dbo.as_all_division_coupons
Where @Recid = RECNO ) AS ar1
ON ao.[date] - 1 = ar1.SCAN_DATE_CONVERT AND
ao.DivNum = ar1.Kma_div_nbr AND
ao.cpnnum = ar1.coupon_nbr
Inner Join (Select *
From dbo.as_all_division_coupons
Where @Recid = RECNO ) AS ar2
ON ao.[date] + 1 = ar2.SCAN_DATE_CONVERT AND
ao.DivNum = ar2.Kma_div_nbr AND
ao.cpnnum = ar2.coupon_nbr
Left Join (Select *
From dbo.as_all_division_coupons
Where @Recid = RECNO ) As ar3
ON ao.[date] = ar3.SCAN_DATE_CONVERT AND
ao.DivNum = ar3.Kma_div_nbr AND
ao.cpnnum = ar3.coupon_nbr
WHERE ar3.STORE IS NULL)
Set @Recid = @Recid - 1
End
Error Msg:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Recid".
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@Recid".
November 15, 2010 at 9:08 am
First, variables are batch-specific. You can't have a batch separator ("GO") between declaring and using. That's your biggest problem. Fix that first, see if you still have errors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2010 at 9:16 am
I took the go out at this line:
Set @Recid = Select MAX(RECNO) as recno from dbo.as_AllStores
Drop Table dbo.as_Redemption_Offers_DayGap
GO
Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.*
......
Still same results...
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Recid".
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@Recid".
November 15, 2010 at 10:46 am
Ok, so I left out 1 or 2 GO. I removed them and now it runs...i have other things i need to add now before running it.
November 15, 2010 at 10:49 am
Glad you got it working.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2010 at 2:26 pm
I want to create a temp table that I can use for my subquery in my loop, so that I don't have to run it 3 times for my links. I tried it but get error msg. I don't need it in the first query but in the loop, it will loop about 1,000 times and they are large tables. Can someone help me with syntax and code?
Declare @StoreNum int
Declare @StoreCnt int
Declare @Recid int
Declare @RecCount int
Set @Recid = (Select MAX(RECNO) as recno from dbo.as_AllStores )
Set @StoreNum = (Select Store From dbo.as_AllStores Where RECNO = @Recid)
Drop Table dbo.as_Redemption_Offers_DayGap
Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.*
Into dbo.as_Redemption_Offers_DayGap
FROM dbo.as_offer_dates as ao
Inner Join (Select *
From dbo.as_all_division_coupons
Where Store = @StoreNum) AS ar1
ON ao.[date] - 1 = ar1.SCAN_DATE_CONVERT AND
ao.DivNum = ar1.Kma_div_nbr AND
ao.cpnnum = ar1.coupon_nbr
Inner Join (Select *
From dbo.as_all_division_coupons
Where Store = @StoreNum) AS ar2
ON ao.[date] + 1 = ar2.SCAN_DATE_CONVERT AND
ao.DivNum = ar2.Kma_div_nbr AND
ao.cpnnum = ar2.coupon_nbr
Left Join (Select *
From dbo.as_all_division_coupons
Where Store = @StoreNum) As ar3
ON ao.[date] = ar3.SCAN_DATE_CONVERT AND
ao.DivNum = ar3.Kma_div_nbr AND
ao.cpnnum = ar3.coupon_nbr
WHERE ar3.STORE IS NULL
Set @Recid = @Recid - 1
While @Recid > 0
begin
Set @StoreNum = (Select Store From dbo.as_AllStores Where RECNO = @Recid)
-- Put into a temp table to reuse in bottom query
--Select *
--Into dbo.##as_OneStore
--From dbo.as_all_division_coupons
--Where Store = @StoreNum
Insert dbo.as_Redemption_Offers_DayGap
Select 'Redemption', ar3.*, 'Offers->', ao.*
FROM dbo.as_offer_dates as ao
Inner Join (Select *
From dbo.as_all_division_coupons
Where Store = @StoreNum ) AS ar1
--Inner Join dbo.##as_OneStore As ar1
ON ao.[date] - 1 = ar1.SCAN_DATE_CONVERT AND
ao.DivNum = ar1.Kma_div_nbr AND
ao.cpnnum = ar1.coupon_nbr
Inner Join (Select *
From dbo.as_all_division_coupons
Where Store = @StoreNum) AS ar2
--Inner Join dbo.##as_OneStore As ar2
ON ao.[date] + 1 = ar2.SCAN_DATE_CONVERT AND
ao.DivNum = ar2.Kma_div_nbr AND
ao.cpnnum = ar2.coupon_nbr
Left Join (Select *
From dbo.as_all_division_coupons
Where Store = @StoreNum) As ar3
--Left Join dbo.##as_OneStore As ar3
ON ao.[date] = ar3.SCAN_DATE_CONVERT AND
ao.DivNum = ar3.Kma_div_nbr AND
ao.cpnnum = ar3.coupon_nbr
WHERE ar3.STORE IS NULL
Set @Recid = @Recid - 1
Drop Table dbo.##as_OneStore
End
November 15, 2010 at 2:34 pm
GrassHopper (11/15/2010)
I want to create a temp table that I can use for my subquery in my loop, so that I don't have to run it 3 times for my links. I tried it but get error msg. I don't need it in the first query but in the loop, it will loop about 1,000 times and they are large tables. Can someone help me with syntax and code?
The error message would help a lot here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 3:23 pm
No error msg this time... Hmmm can't figure it out...I might have left something in it the first time and taken in out with comments when i reran it.
November 16, 2010 at 6:16 am
I'm curious as to why a loop is needed here at all.
It looks to me like what you're doing is taking all of the rows in one table, based on an ID value being higher than the value stored in another table, and then inserting data into that second table from a third table. Is that correct? If so, is there any reason an Insert Select From with a simple join in it wouldn't do this more efficiently?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2010 at 6:58 am
In my subquery (join) table, I have millions of records. When I run the query without the loop it multiplies my records and it gets too big, doesn't even finish. I made the join table smaller by breaking it up by store (a column in my table). There are about 1,000 stores and I filter by that then insert it into the final table. It runs in 2 plus hours. The query without the loop runs for 24 hours and stops because my db grows too large and runs out of space.
November 16, 2010 at 2:42 pm
Ah. That makes sense then.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2010 at 2:48 pm
GrassHopper (11/16/2010)
In my subquery (join) table, I have millions of records. When I run the query without the loop it multiplies my records and it gets too big, doesn't even finish. I made the join table smaller by breaking it up by store (a column in my table). There are about 1,000 stores and I filter by that then insert it into the final table. It runs in 2 plus hours. The query without the loop runs for 24 hours and stops because my db grows too large and runs out of space.
Ah, cursor optimizations. I hate it when we're forced to do those....
If you're stuck at that level you might take a wander through your execution plans and see if you can get more concentrated levels for seeks. Like anything else, there's a balance to how many layers you want to strip off the optimization engine doing it for you, but if you're already on that road might as well give it a full review and make sure you've got the best it can give you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply