-
Insert recs using Loop
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".
-
Remove GO statements from the batch, once you have GO executed, all variable declarations are lost as well.
-
Ok, so I left 1 or 2 GO. I removed them and now it runs...i have other things i need to add now before running it.
-
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
-
-
Using temp table for inner join
ok, so this is the code I'm working with now...The error msg I get is this(I highlighted line 60 in red below):
Msg 2714, Level 16, State 1, Line 60
There is already an object named '##as_OneStore' in the database.
Code:
Declare @StoreNum int
Declare @StoreCnt int
Declare @Recid int
Declare @RecCount int
Set @Recid = (Select MAX(RECNO) as recno from dbo.as_AllStores )
-- Get the Store number to use for subquery below.
Set @StoreNum = (Select Store From dbo.as_AllStores Where RECNO = @Recid)
IF OBJECT_ID(N'tempdb..as_Redemption_Offers_DayGap', N'U') IS NOT NULL
DROP TABLE as_Redemption_Offers_DayGap ;
IF OBJECT_ID(N'tempdb..dbo.##as_OneStore', N'U') IS NOT NULL
DROP TABLE dbo.##as_OneStore ;
Select *
Into dbo.##as_OneStore
From dbo.as_all_division_coupons_count
Where Store = @StoreNum
Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.*
Into dbo.as_Redemption_Offers_DayGap
FROM dbo.as_offer_dates as ao
@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
@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
@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
While @Recid > 0
begin
-- Get the next store number to query in the Join below.
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_count
Where Store = @StoreNum
Insert dbo.as_Redemption_Offers_DayGap
Select 'Redemption', ar3.*, 'Offers->', ao.*
FROM dbo.as_offer_dates as ao
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 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 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
-
Should replace with:
insert into dbo.##as_OneStore
Select * From dbo.as_all_division_coupons_count
Where Store = @StoreNum
Create temp table first then go to loop.
-
I do create the table the first time on top with this line:
Select *
Into dbo.##as_OneStore
From dbo.as_all_division_coupons_count
Where Store = @StoreNum
then i delete the table and try to recreate it with the same query....
It works the first time...the second time is where it bombs out.
-
No, should create it with 'create table ...' instead.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|