[sql2005] update using case statement

[jlbantang]

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

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.

[techgnome]

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

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

[jlbantang]

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

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.