|
-
Apr 11th, 2010, 10:13 AM
#1
Thread Starter
Fanatic Member
[sql2005] update using case statement
Hi there, need extra head once more.
Im updating the dlvrystatus of purchase item against certain condition. The value will be determine through CASE statement. Note that the 2nd and 3rd condition work.
In the code I want to return PENDING for purchase item without deliveries(tblmaterialreceiptdetails).
Code:
update tbllpodetails set dlvrystatus=(
case
--assign Pending for po without deliveries
when is null(select sum(b.qty)
from tblmaterialreceiptdetails b
where b.fk_lpodetailid=tbllpodetails.pk_lpodetailid)
then 'Pending'
--assign Received for lpo without deliveries
when tbllpodetails.qty=(select sum(b.qty)
from tblmaterialreceiptdetails b
where b.fk_lpodetailid=tbllpodetails.pk_lpodetailid)
then 'Received'
else
'Partial'
end
)
Error:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'is'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'then'.
Thanks.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|