I am trying to do an update stateme

[lwrcasep]

I am trying to do an update statement that will update all of my parts and insert the max number of the qty ordered minus the qty recieved. If I received more than I ordered the the number will be a negative which is not correct, so I would like to have it insert a zero if zero is higher than the result of my on order.

I tried the statement below in SQL Server 2000 and received message that Greatest is not a recognized function. I also tried max but this too had issues.

update f_parts set PA_on_order=(Select sum(greatest(FPO_ORDER_QTY-FPO_RECEVIED,0)) FROM F_PO_LINES , F_PO_HEADERS WHERE PO_PK=FPO_PO_FK AND FPO_PART_CODE=PA_CODE AND (PO_CLOSED=0))


Thanks!

 

[MeanGreen]

I don't recognize the greatest function. Is this T-SQL you have here for Microsoft SQL Server? I can't help much if this is for a different database server.

Good Luck.

 

[JohnDTampaBay]

A little embedded CASE logic should do the trick. I have added spacing below for legibility. Give this a shot:

update f_parts
set PA_on_order=
(Select sum(
case when FPO_ORDER_QTY-FPO_RECEVIED > 0
then FPO_ORDER_QTY-FPO_RECEVIED
ELSE 0 END
)
FROM F_PO_LINES , F_PO_HEADERS
WHERE PO_PK=FPO_PO_FK AND FPO_PART_CODE=PA_CODE
AND (PO_CLOSED=0))

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
(0 row(s) affected)

 

[lwrcasep]

That was it. Thanks! I am verifing the data right now.

LWRCasep

 

[lwrcasep]

Well I was wrong. I notice that when I was verifying the data that it populated 9101 on every record. I think that instead of searching on record by record basis it totaled the number of items I have on order. I am going to have to look at the statement again tomorrow. Thanks for the help!