Stored procedure problem... - SQL Server Forums

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)

Stored procedure problem...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-23 : 07:32:10

Rick writes "I have the following sp that I modified by adding another 'and' statement to the 'where' clause. I ownly want this sp to execute if the 'cost' is >= 50. It seems to executing now regardless if the cost is less than 50.

CREATE proc ADG_ProcessMgr_CreateShippers
as
select p.CpnyID,
p.SOOrdNbr,
convert(float, min(p.PriorityScore)) PriorityScore -- make sure this is a float

from (select CpnyID,
SOOrdNbr,
(cast(PlanDate as float) * 1000000)
+ (Priority * 100000)
+ (floor(cast(PriorityDate as float)))
+ (cast(PriorityTime as float) - floor(cast(PriorityTime as float))) PriorityScore
from SOPlan
where PlanType in ('50', '52', '54', '60', '62', '64')) p
join SOHeader h
on h.CpnyID = p.CpnyID
and h.OrdNbr = p.SOOrdNbr

where h.AdminHold = 0
and h.NextFunctionID = '4041000'
and h.NextFunctionClass = ''

-- ================================================================================================
-- Added to the WHERE statement to only run this procedure if COST >=50

and (SELECT sum ( CASE
WHEN (ItemSite.QtyOnHand - (ItemSite.QtyShipNotInv+ItemSite.QtyAlloc))<=0 then 0
WHEN (ItemSite.QtyOnHand - (ItemSite.QtyShipNotInv+ItemSite.QtyAlloc)) < abs(SOplan.Qty) then (ItemSite.QtyOnHand - ItemSite.QtyShipNotInv) * Inventory.StkBasePrc
WHEN (ItemSite.QtyOnHand - (ItemSite.QtyShipNotInv+ItemSite.QtyAlloc)) >= abs(SOplan.Qty) then ABS(SOplan.Qty) * Inventory.StkBasePrc
ELSE 0
END )
AS "Cost"
FROM ItemSite INNER JOIN
SOPlan ON ItemSite.CpnyID = SOPlan.CpnyID AND ItemSite.SiteID = SOPlan.SiteID AND ItemSite.InvtID = SOPlan.InvtID INNER JOIN
Inventory ON SOPlan.InvtID = Inventory.InvtID
WHERE SOPlan.SOOrdNbr = p.SOOrdNbr and plantype = '60') >=50 -- Create shipper if remaining instock dollar amount is greater than this

-- ================================================================================================
group by p.CpnyID,
p.SOOrdNbr

order by p.PriorityScore

-- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.
GO"

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 10:02:25

Don't htink you'll have to worry about:

quote:

-- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.

Can you even begin to descibe in business terms what this is suppose to do?

I would suggest that you take the "cost" out of the predicate and move it up as a derived table and do a join..

That's the best I can come up with...

and what is this?

quote:

+ (floor(cast(PriorityDate as float)))

What meaning does that have?

SELECT (floor(cast(GetdATE() as float)))

(cue Arnold with the infinite cronos god wisdom wagon)

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources