Brain In friday mode please help - S.P.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Brain In friday mode please help - S.P.

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-30 : 12:26:29

Its Friday and my brain is in weekend mode.

I have a simple stored procedure to update a ref # on an order from a ship to master file for a given Customer ID.

dbo.StoredProcedure
(@CCCorderId int)
AS UPDATE dbo.StockOrderHead
SET ShipFromID = dbo.tblArShipFrom.ShipfromID
FROM dbo.StockOrderHead INNER JOIN
dbo.tblArShipFrom ON dbo.StockOrderHead.CustID = dbo.tblArShipFrom.CustId
WHERE (dbo.StockOrderHead.CCCOrderID = @CCCorderId)

How ever I need to make two Changes to it.

1. The dbo.tblArShipFrom Table is one to many and I only want the first entry (order by ShiptoID).
2. If there is no record on the dbo.tblArShipFrom with the CustID then I want to update the dbo.StockOrderHead. ShipFromID = 1111

I Have Come at this from many angles and have failed for the last 140 minutes.

Any Ideas?

Jim
Users <> Logic

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 13:13:13

I'm certainly no expert in sql but here's my try.....

UPDATE dbo.StockOrderHead
SET ShipFromID =(select top 1 isnull(ShipfromID, 1111) from dbo.tblArShipFrom where custid in
(select custId from dbo.StockOrderHead where CCCOrderID=@CCCorderId) order by shiptoID)
WHERE (dbo.StockOrderHead.CCCOrderID = @CCCorderId)

Edited by - label on 05/30/2003 13:23:07

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-30 : 13:37:29

1. The Isnull does not seem to work. (I can work around this)

2. Using the top 1 seems to work but I am conserned that there is no (order by) and I cannot get the sintax right to get it to take (order by)

Jim
Users <> Logic

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-30 : 13:56:05

something like this?

update dbo.StockOrderHead
set ShipFromID =
coalesce((
select top 1 dbo.tblArShipFrom.ShipfromID
from dbo.tblArShipFrom.ShipfromID
where dbo.StockOrderHead.CustID = dbo.tblArShipFrom.CustId
), 1111)
where dbo.StockOrderHead.CCCOrderID = @CCCorderId

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 13:56:13

quote:

1. The Isnull does not seem to work. (I can work around this)

Is your field a varchar or int? If it's a varchar, it may need the single quotes around the "1111" value in the isnull function.

quote:
2. Using the top 1 seems to work but I am conserned that there is no (order by) and I cannot get the sintax right to get it to take (order by)

I'm not sure I understand you, there's an order by in the example I posted.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-30 : 14:10:51

NR I Get an Error

Invalid Object 'dbo.tblArShipFrom.ShipfromID'

Label... Yes you did I somehow missed it. Sorry I had a brain fart.

This Seems to work.

UPDATE dbo.StockOrderHead
SET ShipFromID =
(SELECT TOP 1 (ShipfromID)
FROM dbo.tblArShipFrom
WHERE custid IN
(SELECT custId FROM dbo.StockOrderHead WHERE CCCOrderID = @CCCorderId)Order by ShipfromId)
WHERE (CCCOrderID = @CCCorderId)

UPDATE dbo.StockOrderHead
SET ShipFromID = 3875
WHERE (ShipFromID IS NULL) AND (CCCOrderID = @cccorderID)

The Field is Int I dont understand Why the is null combined with the top statement wont work. M.S. go figure

Jim
Users <> Logic

Edited by - jiml on 05/30/2003 14:15:49

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 14:24:54

quote:

NR I Get an Error

Invalid Object 'dbo.tblArShipFrom.ShipfromID'

Take the "ShipfromID" off of the table to correct that.

quote:
The Field is Int I dont understand Why the is null combined with the top statement wont work. M.S. go figure

Wierd....I've used isnull like that in several similiar instances and not had any issues. Ah well, at least you've got it up and going!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-30 : 14:31:54

Should have seen that...... Fuzzy brain......No sleep.......wife kept me up.......... pestered for more.......Dang midlife crisis.......

Room Spinning.........Vision going dark.........Need.........Need........

Thanks For the help Label and Nr. Have A great weekend

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 14:46:48

quote:

Should have seen that...... Fuzzy brain......No sleep.......wife kept me up.......... pestered for more.......

Count your blessings...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources