SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FIFO QUERY IN SQL Database 10g R2

G.YDec 9 2018 — edited Dec 10 2018

Tables Creation and Inserting Data's

--------------------------------------------------------

-- DDL for Table PUR1

--------------------------------------------------------

CREATE TABLE "INV"."PUR1"

( "PUR_NO" NUMBER(10,0),

"PUR_DATE" DATE,

"ITEM_CODE" NUMBER(10,0),

"ITEM_NAME" VARCHAR2(100 BYTE),

"QTY" NUMBER,

"PUR_RATE" NUMBER(10,3)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "INV_DATA" ;

REM INSERTING into INV.PUR1

SET DEFINE OFF;

Insert into INV.PUR1 (PUR_NO,PUR_DATE,ITEM_CODE,ITEM_NAME,QTY,PUR_RATE) values (1,to_date('01-DEC-18','DD-MON-RR'),123,'ABC',5,2.5);

Insert into INV.PUR1 (PUR_NO,PUR_DATE,ITEM_CODE,ITEM_NAME,QTY,PUR_RATE) values (2,to_date('03-DEC-18','DD-MON-RR'),123,'ABC',3,2);

Insert into INV.PUR1 (PUR_NO,PUR_DATE,ITEM_CODE,ITEM_NAME,QTY,PUR_RATE) values (3,to_date('06-DEC-18','DD-MON-RR'),123,'ABC',7,3.6);

--------------------------------------------------------

-- DDL for Table SALE1

--------------------------------------------------------

CREATE TABLE "INV"."SALE1"

( "SALE_NO" NUMBER(10,0),

"SALE_DATE" DATE,

"ITEM_CODE" NUMBER(10,0),

"ITEM_NAME" VARCHAR2(100 BYTE),

"QTY" NUMBER,

"SALE_RATE" NUMBER(10,3)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "INV_DATA" ;

REM INSERTING into INV.SALE1

SET DEFINE OFF;

Insert into INV.SALE1 (SALE_NO,SALE_DATE,ITEM_CODE,ITEM_NAME,QTY,SALE_RATE) values (1,to_date('04-DEC-18','DD-MON-RR'),123,'ABC',2,3);

Insert into INV.SALE1 (SALE_NO,SALE_DATE,ITEM_CODE,ITEM_NAME,QTY,SALE_RATE) values (2,to_date('07-DEC-18','DD-MON-RR'),123,'ABC',1,4);

-------------------------------

--The Result I want

-----------------------

qury wich i have written

SELECT STOCK_NO, STOCK_DATE, ITEM_CODE, ITEM_NAME, TYPE, QTY, RATE, AMOUNT, STOCK_QTY, STOCK_VALUE,

( CASE WHEN TYPE='IN' THEN SUM(NVL(QTY,0)) OVER (ORDER BY STOCK_DATE)

ELSE

(SUM(NVL(QTY,0)) OVER (ORDER BY STOCK_DATE)) - NVL(QTY,0) END) AS LEFT_STOCK

FROM

(-----PURCHASE QUERY

SELECT 'PUR# '||pur_no AS STOCK_NO, pur_date AS STOCK_DATE, item_code, item_name, 'IN' AS TYPE, QTY, pur_rate AS RATE,

NVL(QTY,0) * NVL(PUR_RATE,0) AS AMOUNT, SUM(NVL(QTY,0)) OVER (ORDER BY PUR_NO) AS STOCK_QTY,

(sum(NVL(QTY,0) * NVL(PUR_RATE,0)) OVER (ORDER BY pur_no)) AS STOCK_VALUE

FROM INV.pur1

UNION ALL

---SALES QUERY

SELECT 'SALE# '||SALE_NO AS STOCK_NO, SALE_DATE AS STOCK_DATE, item_code, item_name, 'OUT' AS TYPE, qty, SALE_RATE AS RATE,

NULL AS AMOUNT, NULL AS STOCK_QTY,----NVL(QTY,0) * NVL(SALE_RATE,0) AS AMOUNT,

NULL AS STOCK_VALUE ---(sum(NVL(QTY,0) * NVL(sale_RATE,0)) OVER (ORDER BY sale_no)) AS STOCK_VALUE

FROM INV.SALE1)A

ORDER BY STOCK_DATE ASC;

STOCK_NO	STOCK_DATE	ITEM_CODE	ITEM_NAME	TYPE	QTY	RATE	AMOUNT	Total Stock	STOCK_VALUE	LEFT_STOCK
PUR# 1	01-DEC-18	123	ABC	IN	5	2.500	12.500	5	12.500	5
PUR# 2	03-DEC-18	123	ABC	IN	3	2.000	6.000	8	18.500	8
SALE# 1	04-DEC-18	123	ABC	OUT	2	3.000	6.000	6	13.500	6
PUR# 3	06-DEC-18	123	ABC	IN	7	3.600	25.200	13	38.700	13
SALE# 2	07-DEC-18	123	ABC	OUT	1	4.000	4.000	12	36.200	12

Added on Dec 9 2018

9 comments

964 views