Helen,

Thanks for your reply. At least now I know there is nothing wrong with my
SP.

Regards,
Regina Phandu

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Monday, February 17, 2003 12:14 PM
Subject: Re: [ib-support] Need help on SP


> 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
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>