Hi, I am new in oracle sql and I would appreciate if you help me.
I have the following tables :
| TABLE: AMOUNTS |
|---|
ID_SEQ NUMBER(12,0) No LINE_NBR NUMBER(12,0) No AMOUNT_ID VARCHAR2(6 BYTE) No I_NBR NUMBER(12,0) No AMOUNT NUMBER(22,5) No |
| ID_SEQ () | LINE_NBR | AMOUNT_ID | I_NBR | AMOUNT |
|---|
| 1 | 1 | 100 | 10 | 500 |
| 2 | 1 | 602 | 10 | 450 |
| 3 | 2 | 121 | 10 | 200 |
| 4 | 2 | 122 | 10 | 350 |
| 5 | 3 | 100 | 10 | 200 |
| 6 | 3 | 100 | 11 | 450 |
| 7 | 3 | 203 | 11 | 210 |
| 8 | 3 | 602 | 11 | 350 |
| 9 | 3 | 122 | 11 | 100 |
| TABLE: AI |
|---|
A_NBR NUMBER(8,0) No I_NBR NUMBER(12,0) No |
| A_NBR | I_NBR |
|---|
| 30 | 10 |
| 31 | 11 |
| TABLE: A |
|---|
A_NBR NUMBER(8,0) No A_DATE DATE Yes |
| A_NBR | A_DATE |
|---|
| 20 | 01-Feb-15 |
| 21 | 02-Aug-15 |
| 30 | 02-Feb-15 |
| 31 | 05-Feb-15 |
CREATE TABLE A
( A_NBR NUMBER(8,0) NOT NULL,
A_DATE DATE,
CONSTRAINT A_pk PRIMARY KEY (A_NBR)
);
CREATE TABLE AI
( A_NBR NUMBER(8,0) NOT NULL,
I_NBR NUMBER(12,0) NOT NULL,
CONSTRAINT AI_pk PRIMARY KEY (I_NBR),
CONSTRAINT A_fk
FOREIGN KEY (A_NBR)
REFERENCES A (A_NBR)
);
CREATE TABLE AMOUNTS
( ID_SEQ NUMBER(12,0) NOT NULL,
LINE_NBR NUMBER(12,0) NOT NULL,
AMOUNT_ID VARCHAR2(6 BYTE) NOT NULL,
I_NBR NUMBER(12,0) NOT NULL,
AMOUNT NUMBER(22,5) NOT NULL,
CONSTRAINT AMOUNTS_pk PRIMARY KEY (ID_SEQ),
CONSTRAINT AMOUNTS_fk
FOREIGN KEY (I_NBR)
REFERENCES AI (I_NBR)
);
INSERT ALL
INTO A VALUES (20, to_date('01/02/2015','mm/dd/yyyy'))
INTO A VALUES (21, to_date('02/08/2015','mm/dd/yyyy'))
INTO A VALUES (30, to_date('02/02/2015','mm/dd/yyyy'))
INTO A VALUES (31, to_date('05/02/2015','mm/dd/yyyy'))
SELECT * FROM dual;
INSERT ALL
INTO AI VALUES (30,10)
INTO AI VALUES (31,11)
SELECT * FROM dual;
INSERT ALL
INTO AMOUNTS VALUES (9,3,'122',11,100)
INTO AMOUNTS VALUES (8,3,'602',11,350)
INTO AMOUNTS VALUES (7,3,'203',11,210)
INTO AMOUNTS VALUES (6,3,'100',11,450)
INTO AMOUNTS VALUES (5,3,'100',10,200)
INTO AMOUNTS VALUES (4,2,'122',10,350)
INTO AMOUNTS VALUES (3,2,'121',10,200)
INTO AMOUNTS VALUES (2,1,'602',10,450)
INTO AMOUNTS VALUES (1,1,'100',10,500)
SELECT * FROM dual;
And I would like to take as result the following output :
parameters are :
Date_From : 01-Feb-15
Date_to : 05-Feb-15
| A_NBR | I_NBR | AMOUNT_100 | AMOUNT_602 | SUM_121_511_122 | AMOUNT_203 |
|---|
| 20 | (null) | (null) | (null) | (null) | (null) |
| 30 | 10 | 700 | 450 | 550 | (null) |
| 31 | 11 | 450 | 350 | 100 | 210 |
Description
I want to find all A-NBR which A_DATE is between Date_From and Date_To (TABLE A),
From these A_NBR to find I_NBR (TABLE AI).
When I retrieve all the I_NBR , for every I_NBR, I want to find the amounts (data from all line_nbr of the same I_NBR)
SUM_121_511_122 is the sum of values with id:121, id=511, id=122 for all the lines of the same I_NBR
My oracle version is 11g
The only amounts there are in AMOUNTS table are 100, 602, 121, 511, 122, 203