Hi
I have a 12c database and am trying to look at achieving an output to a spreadsheet eventually to get information from a couple of tables
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 3 08:43:17 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Aug 03 2020 07:09:57 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
I have created a couple of dummy tables with data in them as such....
DROP TABLE FITMENT_HISTORY_DUMMY;
CREATE TABLE FITMENT_HISTORY_DUMMY
(
FITMENT_HISTORY_OL NUMBER(2) NOT NULL,
FITMENT_HISTORY_ID NUMBER(9) NOT NULL,
AS_TYPE VARCHAR2(15 BYTE),
AS_SERIAL VARCHAR2(16 BYTE),
DATE_START DATE,
DATE_END DATE,
AS_TYPE_PARENT VARCHAR2(15 BYTE),
AS_SERIAL_PARENT VARCHAR2(16 BYTE)
);
DROP TABLE LOCATION_INSTANCE_DUMMY;
CREATE TABLE LOCATION_INSTANCE_DUMMY
(
LOCATION_INSTANCE_OL NUMBER(2) NOT NULL,
LOCATION_INSTANCE_ID NUMBER(9) NOT NULL,
HLA_SERIAL VARCHAR2(6 BYTE) NOT NULL,
LOCATION VARCHAR2(5 BYTE) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE
);
--G01
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 145736, 'G01', 'CF148', TO_DATE('20/03/2002', 'DD/MM/YYYY'), TO_DATE('20/03/2002', 'DD/MM/YYYY'), 'M01', 'DUMMY');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 122592, 'G01', 'CF148', TO_DATE('21/03/2002', 'DD/MM/YYYY'), NULL, 'M01', '014383' );
-- M01
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 99698, 'M01', '014383', TO_DATE('21/06/1989', 'DD/MM/YYYY'), TO_DATE('11/05/1997', 'DD/MM/YYYY'), 'ECU', '7543');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 99699, 'M01', '014383', TO_DATE('12/04/1998', 'DD/MM/YYYY'), TO_DATE('02/09/2001', 'DD/MM/YYYY'), 'ECU', '8596');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 122593, 'M01', '014383', TO_DATE('01/06/2002', 'DD/MM/YYYY'), TO_DATE('04/06/2002', 'DD/MM/YYYY'), 'ECU', '8646');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 123023, 'M01', '014383', TO_DATE('10/07/2002', 'DD/MM/YYYY'), TO_DATE('07/08/2005', 'DD/MM/YYYY'), 'ECU', '8627');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 133908, 'M01', '014383', TO_DATE('13/05/2006', 'DD/MM/YYYY'), TO_DATE('13/11/2013', 'DD/MM/YYYY'), 'ECU', '8589');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 150178, 'M01', '014383', TO_DATE('25/11/2013', 'DD/MM/YYYY'), TO_DATE('19/04/2018', 'DD/MM/YYYY'), 'ECU', '8701');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 161364, 'M01', '014383', TO_DATE('19/04/2018', 'DD/MM/YYYY'), TO_DATE('11/05/2018', 'DD/MM/YYYY'), 'ECU', '8544');
--ECU
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(1, 123145, 'ECU', '8627', TO_DATE('11/08/2002', 'DD/MM/YYYY'), TO_DATE('13/08/2002', 'DD/MM/YYYY'), 'TOR','CS006');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(2, 160158, 'ECU', '8627', TO_DATE('07/10/2002', 'DD/MM/YYYY'), TO_DATE('16/07/2003', 'DD/MM/YYYY'), 'TOR', 'CS028');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(2, 161249, 'ECU', '8627', TO_DATE('04/08/2003', 'DD/MM/YYYY'), TO_DATE('21/09/2004', 'DD/MM/YYYY'), 'TOR','CS072');
Insert into FITMENT_HISTORY_DUMMY
(FITMENT_HISTORY_OL, FITMENT_HISTORY_ID, AS_TYPE, AS_SERIAL, DATE_START, DATE_END, AS_TYPE_PARENT, AS_SERIAL_PARENT)
Values
(2, 162648, 'ECU', '8627', TO_DATE('03/10/2004', 'DD/MM/YYYY'), TO_DATE('06/07/2005', 'DD/MM/YYYY'), 'TOR', 'CS061');
Insert into LOCATION_INSTANCE_DUMMY
(LOCATION_INSTANCE_OL, LOCATION_INSTANCE_ID, HLA_SERIAL, LOCATION, START_DATE, END_DATE)
Values
(1, 74, 'CS006', 'DHA', TO_DATE('29/12/1993', 'DD/MM/YYYY'), TO_DATE('01/05/2006 10:40:00', 'DD/MM/YYYY HH24:MI:SS'));
Insert into LOCATION_INSTANCE_DUMMY
(LOCATION_INSTANCE_OL, LOCATION_INSTANCE_ID, HLA_SERIAL, LOCATION, START_DATE, END_DATE)
Values
(1, 267, 'CS028', 'DHA', TO_DATE('19/02/2003', 'DD/MM/YYYY'), TO_DATE('08/04/2003 13:16:15', 'DD/MM/YYYY HH24:MI:SS'));
Insert into LOCATION_INSTANCE_DUMMY
(LOCATION_INSTANCE_OL, LOCATION_INSTANCE_ID, HLA_SERIAL, LOCATION, START_DATE, END_DATE)
Values
(1, 278, 'CS028', 'KHA',TO_DATE('08/04/2003 13:17:09', 'DD/MM/YYYY HH24:MI:SS'), TO_DATE('09/04/2003 07:46:24', 'DD/MM/YYYY HH24:MI:SS'));
Insert into LOCATION_INSTANCE_DUMMY
(LOCATION_INSTANCE_OL, LOCATION_INSTANCE_ID, HLA_SERIAL, LOCATION, START_DATE, END_DATE)
Values
(1, 280, 'CS028', 'DHA', TO_DATE('09/04/2003 07:47:08', 'DD/MM/YYYY HH24:MI:SS'), TO_DATE('09/04/2003 07:49:07', 'DD/MM/YYYY HH24:MI:SS'));
Insert into LOCATION_INSTANCE_DUMMY
(LOCATION_INSTANCE_OL, LOCATION_INSTANCE_ID, HLA_SERIAL, LOCATION, START_DATE, END_DATE)
Values
(2, 329, 'CS028', 'KHA', TO_DATE('07/08/1991', 'DD/MM/YYYY'), TO_DATE('12/01/2003 15:50:26', 'DD/MM/YYYY HH24:MI:SS'));
Insert into LOCATION_INSTANCE_DUMMY
(LOCATION_INSTANCE_OL, LOCATION_INSTANCE_ID, HLA_SERIAL, LOCATION, START_DATE, END_DATE)
Values
(2, 419, 'CS028', 'KHA', TO_DATE('09/04/2003', 'DD/MM/YYYY'), TO_DATE('06/03/2005 10:38:18', 'DD/MM/YYYY HH24:MI:SS'));
COMMIT;
I am hoping to create an sql statement to get all the info I need rather than via an sql package with various cursors to get relevant info with data into temporary tables then spool them to a file to then eventually created a csv file for output
Firstly the query itself to start with to understand what I am trying to do in terms of data to get back...
SELECT FITMENT_HISTORY_OL,
FITMENT_HISTORY_ID,
AS_TYPE,
AS_SERIAL,
DATE_START,
DATE_END,
AS_TYPE_PARENT,
AS_SERIAL_PARENT
FROM fitment_history_dummy f
where f.as_type = 'G01'
and f.as_serial = 'CF148';
FITMENT_HISTORY_OL FITMENT_HISTORY_ID AS_TYPE AS_SERIAL DATE_STAR DATE_END AS_TYPE_PARENT AS_SERIAL_PARENT
1 145736 G01 CF148 20-MAR-02 20-MAR-02 M01 DUMMY
1 122592 G01 CF148 21-MAR-02 M01 014383
2 rows are retrieved for as_type=G01, as_serial=CG148 ie its parents with date_start and date_end dates
So for each row I need to find IT'S parent within that date range ...
A query on its own would be something like ..
SELECT FITMENT_HISTORY_OL,
FITMENT_HISTORY_ID,
AS_TYPE,
AS_SERIAL,
DATE_START,
DATE_END,
AS_TYPE_PARENT,
AS_SERIAL_PARENT
FROM fitment_history_dummy f
where f.as_type = 'M01'
and f.as_serial = 'DUMMY'
and ((ld_date_start < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and ld_date_end is null) or (nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) = nvl(ld_date_end, to_date('31/12/2049','dd/mm/yyyy'))) or ( ld_date_start < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and nvl(ld_date_end, to_date('31/12/2049','dd/mm/yyyy')) > date_start))
ORDER BY sequence, date_start;
where ld_date_start = '20-MAR-02'
and ld_date_end = '20-MAR-02'
This would bring back no records as there are no M01, DUMMY records within that date range (or in fact any records)
As there were two records the second record would bring back data using the query as above substituting values for date_start and date_end in the query ...
SELECT FITMENT_HISTORY_OL,
FITMENT_HISTORY_ID,
AS_TYPE,
AS_SERIAL,
DATE_START,
DATE_END,
AS_TYPE_PARENT,
AS_SERIAL_PARENT
FROM fitment_history_dummy f
where f.as_type = 'M01'
and f.as_serial = '014383'
and (('21-MAR-02' < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and '' is null) or (nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) = nvl('', to_date('31/12/2049','dd/mm/yyyy'))) or ( '21-MAR-02' < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and nvl('', to_date('31/12/2049','dd/mm/yyyy')) > date_start))
ORDER BY date_start;
where ld_date_start = '21-MAR-022'
and ld_date_end is NULL (ie an open end date).
FITMENT_HISTORY_OL FITMENT_HISTORY_ID AS_TYPE AS_SERIAL DATE_STAR DATE_END AS_TYPE_PARENT AS_SERIAL_PARENT
1 122593 M01 014383 01-JUN-02 04-JUN-02 ECU 8646
1 123023 M01 014383 10-JUL-02 07-AUG-05 ECU 8627
1 133908 M01 014383 13-MAY-06 13-NOV-13 ECU 8589
1 150178 M01 014383 25-NOV-13 19-APR-18 ECU 8701
1 161364 M01 014383 19-APR-18 11-MAY-18 ECU 8544
So now each parent of M01, 014383 theres a new parent within a new date_start and date_end range eg
ECU 8646 01-JUN-02 04-JUN-02
ECU 8627 10-JUL-02 07-AUG-05
ECU 8589 13-MAY-06 13-NOV-13
ECU 8701 25-NOV-13 19-APR-18
ECU 8544 19-APR-18 11-MAY-18
So now using the date range retrieved here get IT's parent ...
SELECT FITMENT_HISTORY_OL,
FITMENT_HISTORY_ID,
AS_TYPE,
AS_SERIAL,
DATE_START,
DATE_END,
AS_TYPE_PARENT,
AS_SERIAL_PARENT
FROM fitment_history_dummy f
where f.as_type = 'ECU'
and f.as_serial = '8646'
and (('01-JUN-02' < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and '04-JUN-02' is null) or (nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) = nvl('04-JUN-02', to_date('31/12/2049','dd/mm/yyyy'))) or ( '01-JUN-02' < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and nvl('04-JUN-02', to_date('31/12/2049','dd/mm/yyyy')) > date_start))
ORDER BY date_start;
brings back no records as none exist for ECU, 8646 within that date range..
Looking at the next record...
SELECT FITMENT_HISTORY_OL,
FITMENT_HISTORY_ID,
AS_TYPE,
AS_SERIAL,
DATE_START,
DATE_END,
AS_TYPE_PARENT,
AS_SERIAL_PARENT
FROM fitment_history_dummy f
where f.as_type = 'ECU'
and f.as_serial = '8627'
and (('10-JUL-02' < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and '07-AUG-05' is null) or (nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) = nvl('07-AUG-05', to_date('31/12/2049','dd/mm/yyyy'))) or ( '10-JUL-02' < nvl(date_end, to_date('31/12/2049','dd/mm/yyyy')) and nvl('07-AUG-05', to_date('31/12/2049','dd/mm/yyyy')) > date_start))
ORDER BY date_start;
brings back...
FITMENT_HISTORY_OL FITMENT_HISTORY_ID AS_TYPE AS_SERIAL DATE_STAR DATE_END AS_TYPE_PARENT AS_SERIAL_PARENT
1 123145 ECU 8627 11-AUG-02 13-AUG-02 TOR CS006
2 160158 ECU 8627 07-OCT-02 16-JUL-03 TOR CS028
2 161249 ECU 8627 04-AUG-03 21-SEP-04 TOR CS072
2 162648 ECU 8627 03-OCT-04 06-JUL-05 TOR CS061
I will need to do the same for each of the ECU records for its date range but wont display results here as it will get too long..
Finally having retrieved the above records for each ECU record I need to use that to look at data from the second table using the date
range for the ECU retrieved from the previous query ie between 10-JUL-02 and 07-AUG-05 and the parent retrieved above ...
For example for ECU, 8627 its parent serial was CS006 between 11-AUG-02 AND 13-AUG-02 , and CS028 between 07-OCT-02 and 16-JUL-03 etc...
select l.hla_serial,
l.location,
GREATEST('07-OCT-02' , l.start_date) start_date,
LEAST (NVL('16-JUL-03',SYSDATE),l.end_date) end_date
from location_instance_dummy l
where l.hla_serial = 'CS028'
and (('07-OCT-02' < nvl(end_date, to_date('31/12/2049','dd/mm/yyyy')) and '16-JUL-03' is null) or (nvl(end_date, to_date('31/12/2049','dd/mm/yyyy')) = nvl('16-JUL-03', to_date('31/12/2049','dd/mm/yyyy'))) or ( '07-OCT-02' < nvl(end_date, to_date('31/12/2049','dd/mm/yyyy')) and nvl('16-JUL-03', to_date('31/12/2049','dd/mm/yyyy')) > start_date))
ORDER BY l.start_date;
So if the date range specified for the ECU 8627 (10-JUL-02 and 07-AUG-05) falls within the date range of its parent eg CS028 in the 'location_instance_dummy' table bring back the data for that row
HLA_SE LOCAT START_DAT END_DATE
CS028 KHA 07-AUG-91 12-JAN-03
CS028 DHA 19-FEB-03 08-APR-03
CS028 KHA 08-APR-03 09-APR-03
CS028 KHA 09-APR-03 06-MAR-05
CS028 DHA 09-APR-03 09-APR-03
In summary.......
I am trying to get parent info for an as_type,as_serial along with the start and end dates then using those in the same query to get its parent .
This is done until the grandparent is reached upon which using that as_serial I can retrieve the data for it in another table using the date range
retrieved from the first table.
I am not too great on using WITH and connect_by prior etc as that seems to be the way forward but I am trying to avoid writing a package and
creating temporary tables in order to get the info to put into the temporay tables then finally spooling the results to a file.
The final results I am trying to achieve should be something like the attached screenshot...
Regards
