Help in Query to achieve output? [message #663193] |
Thu, 25 May 2017 02:03 |
|
arifs3738
Messages: 39 Registered: November 2015 Location: India
|
Member |
|
|
TABLE SCRIPTS
CREATE TABLE T45
(
REGION VARCHAR2(15),
DEPTNO NUMBER,
START_DATE DATE,
END_DATE DATE
);
INSERT INTO T45 VALUES ('IND',10,TO_DATE('01-04-2017','DD-MM-YYYY'),TO_DATE('04-04-2017','DD-MM-YYYY'));
INSERT INTO T45 VALUES ('US',20,TO_DATE('05-04-2017','DD-MM-YYYY'),TO_DATE('07-04-2017','DD-MM-YYYY'));
INSERT INTO T45 VALUES ('UK',10,TO_DATE('08-04-2017','DD-MM-YYYY'),TO_DATE('10-04-2017','DD-MM-YYYY'));
COMMIT;
***Source_Data***
REGION DEPTNO START_DATE END_DATE
IND 10 01-APR-2017 00:00:00 04-APR-2017 00:00:00
US 20 05-APR-2017 00:00:00 07-APR-2017 00:00:00
UK 30 08-APR-2017 00:00:00 10-APR-2017 00:00:00
***Target_Data***
REGION DEPTNO DATE
IND 10 01-APR-2017 00:00:00
IND 10 02-APR-2017 00:00:00
IND 10 03-APR-2017 00:00:00
IND 10 04-APR-2017 00:00:00
US 20 05-APR-2017 00:00:00
US 20 06-APR-2017 00:00:00
US 20 07-APR-2017 00:00:00
UK 30 08-APR-2017 00:00:00
UK 30 09-APR-2017 00:00:00
UK 30 10-APR-2017 00:00:00
My Query:
SELECT A.REGION, A.DEPTNO, END_DATE - START_DATE AS DAYS_DIFF FROM T45 A
INNER JOIN
(SELECT LEVEL AS L FROM DUAL CONNECT BY LEVEL <= (SELECT END_DATE - START_DATE AS DT FROM T45)) B
ON B.L <= A.DAYS_DIFF;
Not getting the above output. Please suggest me where to make changes to get the above output
|
|
|
|
|
|
|
|
|
|
Re: Help in Query to achieve output? [message #663274 is a reply to message #663251] |
Sat, 27 May 2017 08:46 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
arifs3738 wrote on Fri, 26 May 2017 22:54
Finally Achieved the output after going thru the post of connect by level suggested by Michel Cadot.
You picked probably worst method:
SQL> explain plan for
2 SELECT REGION, DEPTNO, START_DATE+L-1 AS START_DATE FROM T45
3 INNER JOIN
4 (SELECT LEVEL AS L FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(END_DATE-START_DATE) +1 AS DT FRO
5 ON END_DATE-START_DATE+1>=B.L
6 ORDER BY REGION, START_DATE;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1123901456
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 53 | 6 (17)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 53 | 5 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 18 | | |
| 7 | TABLE ACCESS FULL | T45 | 3 | 54 | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T45 | 1 | 40 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(LEVEL<= (SELECT MAX("END_DATE"-"START_DATE")+1 FROM "T45" "T45"))
8 - filter("B"."L"<="END_DATE"-"START_DATE"+1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
25 rows selected.
SQL> explain plan for
2 SELECT REGION, DEPTNO, START_DATE+LEVEL-1 AS START_DATE FROM T45
3 CONNECT BY ROWID = PRIOR ROWID
4 AND START_DATE+LEVEL-1 <= END_DATE
5 AND PRIOR SYS_GUID() IS NOT NULL;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 127596909
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 156 | 3 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHOUT FILTERING| | | | | |
| 2 | TABLE ACCESS FULL | T45 | 3 | 156 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID=PRIOR ROWID)
filter("END_DATE">=INTERNAL_FUNCTION("START_DATE")+LEVEL-1 AND PRIOR
SYS_GUID() IS NOT NULL)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
20 rows selected.
SQL>
And you didn't post oracle version. LATERAL or CROSS APPLY in 12C is fastest solution:
SQL> set timing on
SQL> create table tbl
2 as
3 select ename,
4 n
5 from emp
6 cross apply(
7 select level n
8 from dual
9 connect by level <= empno
10 )
11 /
Table created.
Elapsed: 00:00:00.12
SQL> drop table tbl purge
2 /
Table dropped.
Elapsed: 00:00:00.01
SQL> create table tbl
2 as
3 select ename,
4 n
5 from emp,
6 lateral(
7 select level n
8 from dual
9 connect by level <= empno
10 )
11 /
Table created.
Elapsed: 00:00:00.12
SQL> drop table tbl purge
2 /
Table dropped.
Elapsed: 00:00:00.02
SQL> create table tbl
2 as
3 select ename,
4 level n
5 from emp
6 connect by level <= empno
7 and prior ename = ename
8 and prior sys_guid() is not null
9 /
Table created.
Elapsed: 00:00:02.16
SQL>
SY.
|
|
|