[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.

I'ts possible... just need to get the syntax correct.... based on what you have, see if this works:

Code:

---

UPDATE tbllpodetails
SET dlvrystatus =
        CASE
              WHEN Q.totalQty IS NULL THEN 'Pending'
              WHEN Q.totalQty = tbllpodetails.qty THEN 'Received'
              ELSE 'Partial'
        END

FROM tbllpodetails
LEFT JOIN (
                select a.pk_lpodetailid, sum(b.qty) at otalQty
                from        tbllpodetails a
                inner join tblmaterialreceiptdetails b
                  on b.fk_lpodetailid=a.pk_lpodetailid
) Q
On tbllpodetails.pk_lpodetailid = Q.pk_lpodetailid

---

See if that fits what you need.

-tg

I received this error

Column 'tbllpodetails.pk_lpodetailid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

and I add the grouping

Code:

---

LEFT JOIN (
                select a.pk_lpodetailid, sum(b.qty) as totalQty
                from        tbllpodetails a
                inner join tblmaterialreceiptdetails b
                on b.fk_lpodetailid=a.pk_lpodetailid
                                group by pk_lpodetailid
) Q

---

But i did not get the correct result. The status Received flags lpo item with partial deliveries. Im close to it though.


Thanks for dropping by.