At 10:19 AM 17/02/2003 +0700, Regina Phandu wrote:

>Hi,
>
>I need help on my SP. There's no error when I execute procedure. But, it
>seems that I didn't get any results.
>
>create procedure sp_test
>as
> declare variable v_sku_no varchar(10);
> declare variable v_size_code varchar(10);
> declare variable v_qty decimal(12,2);
>begin
> for select t_sku_no,t_size_code,sum(t_qty)
> from pos_transaction_dtl
><-------------------------
> group by t_sku_no,t_size_code
> into :v_sku_no,:v_size_code,:v_qty
> do
> begin
> update product_location set qty = qty - :v_qty
><-------------------------
> where sku_no = :v_sku_no
> and size_code = :v_size_code
> and loc_type = 'D';
> end
>end
>
>Basically, I need to substract quantity of a product in my warehouse with
>quantity from transaction.
>Table pos_transaction_dtl holds information about any transactions including
>quantity being sold. and table product_location holds all of my products.
>So, this SP update the quantity on product_location from quantity on
>pos_transaction_dtl.
>
> >From my point of view, there's nothing wrong with my SP but it seems that
>the variables of v_sku_no and v_size_code are not work properly.
>
>Can anyone help me?

Regina,
I can't see anything wrong with the SP. If you are sure there are eligible
data, have you checked the integrity of the SKU and/or size codes? You
won't get output here unless you get a completely literal match between
those codes. One blank space more or less will break it. If human beings
enter these codes then the potential for error is pretty strong. You have
potential to "lose" rows both in the group by and when the location and
transaction records don't match.

heLen