variable from a select to an insert 3

[EchoAlertcom]

Hello,

I need help with variables. I have copied some sql below. What I need to do is. Make the variable @HelpMe = the value from oi.catalogid and then use the variable in the insert statement below. Thank you for all your help.

SELECT	oi.catalogid,
        oi.orderid,
        o.orderid

FROM	shopping400.dbo.oitems oi INNER JOIN shopping400.dbo.orders o
ON 	oi.orderid = o.orderid

WHERE	oi.orderid = (Select max(shopping400.dbo.oitems.orderid) from shopping400.dbo.oitems)

DECLARE @HelpMe int(4)
SET @HelpMe = oi.catalogid

INSERT INTO EchoAlert.dbo.A_ClientMaster(InitialPlanID) VALUES (@HelpMe)
GO

Sincerely,
Steve Funk

 

[mwolf00]

Since you can only assign 1 number to @helpMe:

DECLARE @HelpMe int(4)
SET @HelpMe = TOP 1 oi.catalogid FROM shopping400.dbo.oitems oi INNER JOIN shopping400.dbo.orders o
ON oi.orderid = o.orderid

WHERE oi.orderid = (Select max(shopping400.dbo.oitems.orderid) from shopping400.dbo.oitems)

-- What did you expect? This is FREE advice. LOL

 

[MeanGreen]

I think what you are trying to do is this:

INSERT INTO EchoAlert.dbo.A_ClientMaster(InitialPlanID) VALUES (SELECT oi.catalogid
FROM shopping400.dbo.oitems oi INNER JOIN shopping400.dbo.orders o
ON oi.orderid = o.orderid
WHERE oi.orderid = (Select max(shopping400.dbo.oitems.orderid) from shopping400.dbo.oitems)
)


Then you wouldn't need the variable at all.

Hope this helps.

 

[mwolf00]

I was thinking that he might have created the variable for some other use in the SP... Otherwise, I agree w/ MeanGreen. -- What did you expect? This is FREE advice. LOL

 

[EchoAlertcom]

Thank you both for your answers.

MeanGreen (or anyone) I ran your script and this was the error message. Any hints?

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.

Sincerely,
Steve

 

[EchoAlertcom]

for some additional info. I tried mwolf00's solution and got an rerror that stated that there was a syntax error around TOP which is kind of like select i think. and that is the error from MeanGreen's.

When I run just the select portion it runs fine.

Sincerely,.
Steve FUnk

 

[sunila7]

Hi,

mwolf's code should be

DECLARE @HelpMe int(4)

SELECT TOP 1 @HelpMe = oi.catalogid FROM shopping400.dbo.oitems oi INNER JOIN shopping400.dbo.orders o
ON oi.orderid = o.orderid

INSERT INTO EchoAlert.dbo.A_ClientMaster(InitialPlanID) VALUES (@HelpMe)


Hope it helps ..............

Sunil

 

[mwolf00]

OOPS!

DECLARE @HelpMe int(4)
SELECT TOP 1 @HelpMe = oi.catalogid FROM shopping400.dbo.oitems oi INNER JOIN shopping400.dbo.orders o
ON oi.orderid = o.orderid
WHERE oi.orderid = (Select max(shopping400.dbo.oitems.orderid) from shopping400.dbo.oitems) ORDER BY o.orderID

-- What did you expect? This is FREE advice. LOL

 

[mwolf00]

Thanks Sunil, I think we both spotted that one at the same time...
-- What did you expect? This is FREE advice. LOL

 

[MeanGreen]

Can you give some details about the a_clientMaster table? Also, some general idea about the shopping400 table. What is it you are trying to store in the A_ClientMaster table... just the catalog_Id or just unique catalog_id or other information with the catalog_ID? Try running just this SQL (since I copied it from your original) and see if it returns values:

SELECT oi.catalogid
FROM shopping400.dbo.oitems oi INNER JOIN shopping400.dbo.orders o
ON oi.orderid = o.orderid
WHERE oi.orderid = (Select max(shopping400.dbo.oitems.orderid) from shopping400.dbo.oitems)

Other than that, it all looks fine. If you could answer these questions we will try to provide an appropriate solution for you.

Hope this helps.

 

[MeanGreen]

Also... the insert should be:

INSERT INTO EchoAlert.dbo.A_ClientMaster(InitialPlanID) SELECT oi.catalogid
FROM shopping400.dbo.oitems oi INNER JOIN shopping400.dbo.orders o
ON oi.orderid = o.orderid
WHERE oi.orderid = (Select max(shopping400.dbo.oitems.orderid) from shopping400.dbo.oitems)

There is no Values statement.

Hope this helps.

 

[EchoAlertcom]

Thank you everyone for all the help.

mwolf00
Why is ORDER BY o.orderID needed?

and if i want the largest number should i use

ORDER BY o.orderID DESC?


MeanGreen, Your solution is working as well. I made a mistake with the i needed to do an update not an inert.
I have modified what you have sent and it is working great.


Again I want to thank everyone for the help.

Sinerely,
Steve Funk

 

[mwolf00]

When using SELECT TOP 1 you should use an order by clause to get just the result you want (remember, we are using it in case your query returns more than 1 result). You can order by any criteria you want (so the largest orderID would be o.orderID DESC). -- What did you expect? This is FREE advice. LOL