Hi,
I want to calculate inventory with FIFO METHOD. my tabel is :
inventory(doc char(10),tgl date, qty numeric(15,2), price
numeric(15,2))
doc tgl PRICE QTY
---
FP123 02/02/06 180 10
ASD12 07/02/06 -9
FP23 10/02/06 150 2
ASD14 11/02/06 -2
I want to result is as below:
doc tgl price qty
---
FP123 02/02/06 180 10
ASD12 07/02/06 180 -9
FP23 10/02/06 150 2
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
how to create sintak SQL SERVER ?
thank you for your advanced.
best regards,
alimIf I understand your problem correctly , the sybtax is;
SELECT doc,tgl,qty,price ORDER BY tgl ASC
Jack Vamvas
___________________________________
Receive free SQL tips - http://www.ciquery.com/sqlserver.htm
"alim" <alfen_lim@.yahoo.com> wrote in message
news:1141293436.115783.286230@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to calculate inventory with FIFO METHOD. my tabel is :
> inventory(doc char(10),tgl date, qty numeric(15,2), price
> numeric(15,2))
> doc tgl PRICE QTY
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 -2
> I want to result is as below:
> doc tgl price qty
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 180 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 180 -1
> ASD14 11/02/06 150 -1
> how to create sintak SQL SERVER ?
> thank you for your advanced.
> best regards,
> alim
>|||hi jack,
cannot used syntax like :SELECT doc,tgl,qty,price ORDER BY tgl ASC
because
in tabel : ASD14 11/02/06 -2
I want to :
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
best regards,
alim|||Have you been over to www.dbazine.com and looked for my article on
inventory?
Showing posts with label char. Show all posts
Showing posts with label char. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
invalid row id
with this code:
SET SERVEROUTPUT ON
CREATE OR REPLACE
PROCEDURE update_price(p_cutoff IN CHAR) AS
change CONSTANT Real := 0.9;
CURSOR c_upgrade_p IS
SELECT price FROM (select price, start_date from items i, on_sale os
WHERE (i.id = os.item_id))
WHERE (TO_DATE(start_date,'DD-MON-YYYY') = TO_DATE(p_cutoff,'DD-MON-YYYY'))
FOR UPDATE OF PRICE;
BEGIN
FOR record IN c_upgrade_p LOOP
UPDATE items
SET price = price * 0.9
WHERE CURRENT OF c_upgrade_p;
END LOOP;
COMMIT;
END;
/
exec update_price('30-AUG-03');
i get an invalid row id error, why is this?Try it without the inline view:
CURSOR c_upgrade_p IS
SELECT price
from items i, on_sale os
WHERE (i.id = os.item_id))
WHERE (TO_DATE(start_date,'DD-MON-YYYY') = TO_DATE(p_cutoff,'DD-MON-YYYY'))
FOR UPDATE OF PRICE;
BTW, does this: TO_DATE(start_date,...) mean that start_date is not a DATE?|||the start_date is a date, i am just checking that the format is the same... i dont know how to change from inline...?|||If start_date is a DATE, then don't use TO_DATE on it. And I showed you the version without the inline view, though I made a mistake or two. Here is the corrected version, with the unwanted TO_DATE removed:
CURSOR c_upgrade_p IS
SELECT price
from items i, on_sale os
WHERE i.id = os.item_id
AND start_date = TO_DATE(p_cutoff,'DD-MON-YYYY')
FOR UPDATE OF PRICE;
A DATE column doesn't have any format, it is stored in a special internal coding. Only use TO_DATE to convert a text string to a DATE, and use TO_CHAR to format a DATE for display.|||thanks heaps. ill implement it today and see what happens!
SET SERVEROUTPUT ON
CREATE OR REPLACE
PROCEDURE update_price(p_cutoff IN CHAR) AS
change CONSTANT Real := 0.9;
CURSOR c_upgrade_p IS
SELECT price FROM (select price, start_date from items i, on_sale os
WHERE (i.id = os.item_id))
WHERE (TO_DATE(start_date,'DD-MON-YYYY') = TO_DATE(p_cutoff,'DD-MON-YYYY'))
FOR UPDATE OF PRICE;
BEGIN
FOR record IN c_upgrade_p LOOP
UPDATE items
SET price = price * 0.9
WHERE CURRENT OF c_upgrade_p;
END LOOP;
COMMIT;
END;
/
exec update_price('30-AUG-03');
i get an invalid row id error, why is this?Try it without the inline view:
CURSOR c_upgrade_p IS
SELECT price
from items i, on_sale os
WHERE (i.id = os.item_id))
WHERE (TO_DATE(start_date,'DD-MON-YYYY') = TO_DATE(p_cutoff,'DD-MON-YYYY'))
FOR UPDATE OF PRICE;
BTW, does this: TO_DATE(start_date,...) mean that start_date is not a DATE?|||the start_date is a date, i am just checking that the format is the same... i dont know how to change from inline...?|||If start_date is a DATE, then don't use TO_DATE on it. And I showed you the version without the inline view, though I made a mistake or two. Here is the corrected version, with the unwanted TO_DATE removed:
CURSOR c_upgrade_p IS
SELECT price
from items i, on_sale os
WHERE i.id = os.item_id
AND start_date = TO_DATE(p_cutoff,'DD-MON-YYYY')
FOR UPDATE OF PRICE;
A DATE column doesn't have any format, it is stored in a special internal coding. Only use TO_DATE to convert a text string to a DATE, and use TO_CHAR to format a DATE for display.|||thanks heaps. ill implement it today and see what happens!
Subscribe to:
Comments (Atom)