Need some help in solving this query..!!!

Question

Need some help in solving this query..!!!

kerobin jesu

Mr or Mrs. 500

Points: 544
More actions
May 29, 2007 at 6:24 am

#178847

Hi all
when i run this query..
IF(@EnquiryChannel = 'ALL')
BEGIN
SELECT BK.Booking_ID, Sum(BKL.Quantity) As Quantity
INTO #TempWeeks
FROM
[SalesOP] SO (NOLOCK)
INNER JOIN [SalesOffice_lkp] SOL (NOLOCK)
ON SOL.[Code] = SO.[SalesOfficeCode]
INNER JOIN [Customer] CU (NOLOCK)
ON CU.Customer_Id = SO.Customer_Id
INNER JOIN [Address] AD (NOLOCK)
ON AD.Customer_Id = SO.Customer_Id
LEFT JOIN [Booking] BK (NOLOCK)
ON SO.[SalesOP_id] = BK.[SalesOP_id]
Left JOIN [BookingLine] BKL (NoLock)
ON BK.Booking_ID = BKL.Booking_ID
WHERE
(SO.IsOriginalEnquiry = 1)
AND (SO.IsDummyEnquiry = 0)
AND (SOL.SalesCountryCode = @vcCountryCode)
AND (@vcSalesOffice = 'ALL' OR SO.[SalesOfficeCode] = @vcSalesOffice)
AND (@vcProduct = 'ALL' OR SO.[ProductCode] = @vcProduct)
AND (@vcProgram = 'ALL' OR SO.[ProgramCode] = @vcProgram)
AND (SO.[LocalInsertDate] BETWEEN @dtFromDate and @dtToDate)
--AND (@EnquiryChannel = 'ALL' OR SO.EnquiryChannel = @EnquiryChannel)
AND BKL.article_id <= 0 and BKL.StatusCode = 'AC'
Group BY bk.Booking_ID
END
IF(@EnquiryChannel!='ALL')
BEGIN
SELECT BK.Booking_ID, Sum(BKL.Quantity) As Quantity
INTO #TempWeeks
FROM
[SalesOP] SO (NOLOCK)
INNER JOIN [SalesOffice_lkp] SOL (NOLOCK)
ON SOL.[Code] = SO.[SalesOfficeCode]
INNER JOIN [Customer] CU (NOLOCK)
ON CU.Customer_Id = SO.Customer_Id
INNER JOIN [Address] AD (NOLOCK)
ON AD.Customer_Id = SO.Customer_Id
LEFT JOIN [Booking] BK (NOLOCK)
ON SO.[SalesOP_id] = BK.[SalesOP_id]
Left JOIN [BookingLine] BKL (NoLock)
ON BK.Booking_ID = BKL.Booking_ID
WHERE
(SO.IsOriginalEnquiry = 1)
AND (SO.IsDummyEnquiry = 0)
AND (SOL.SalesCountryCode = @vcCountryCode)
AND (@vcSalesOffice = 'ALL' OR SO.[SalesOfficeCode] = @vcSalesOffice)
AND (@vcProduct = 'ALL' OR SO.[ProductCode] = @vcProduct)
AND (@vcProgram = 'ALL' OR SO.[ProgramCode] = @vcProgram)
AND (SO.[LocalInsertDate] BETWEEN @dtFromDate and @dtToDate)
AND (SO.EnquiryChannel = @EnquiryChannel)
AND BKL.article_id <= 0 and BKL.StatusCode = 'AC'
Group BY bk.Booking_ID
END
am getting the followin error..
Msg 2714, Level 16, State 6, Line 39
There is already an object named '#TempWeeks' in the database
Can anyone help me to fix this..??
Thanks in Advance...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Basically the problem is that the parser thinks that both "select into" commands are executed.

The easiest way to solve this is defining the temp table at the beginning and then do a simple insert instead of select into.

CREATE TABLE #TempWeeks1

Booking_id int, Quantity int)

IF(@EnquiryChannel = 'ALL')

BEGIN

INSERT INTO #TempWeeks1

SELECT BK.Booking_ID, Sum(BKL.Quantity) As Quantity

FROM ....

Markus

[font="Verdana"]Markus Bohse[/font]

kerobin jesu Mr or Mrs. 500 Points: 544 More actions · Answer 2

Hey

Am sorry..!!

That table name is not #TempWeeks1.. its #TempWeeks

I update it again...

Can u look at it now..??

Thanks again!!!

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 3

Are you creating the temp table then not dropping it when you are finished?

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

SELECT INTO will always fail if the table already exists so you have two options:

predefine the temporary table then INSERT INTO .... SELECT in both If instances
SELECT ... INTO in the first if instance and then INSERT INTO ... SELECT

Also, make sure you drop the #tempweeks temporary table when you're finished with it.