Home » SQL & PL/SQL » SQL & PL/SQL » Help in Query to achieve output? (Oracle 11g)
Help in Query to achieve output? [message #663193] Thu, 25 May 2017 02:03 Go to next message
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 #663196 is a reply to message #663193] Thu, 25 May 2017 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

date generator

Re: Help in Query to achieve output? [message #663200 is a reply to message #663196] Thu, 25 May 2017 03:34 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
SELECT REGION, DEPTNO, START_DATE+L AS C_DT FROM T45 A,
(SELECT LEVEL-1 AS L FROM DUAL
CONNECT BY LEVEL <= (SELECT END_DATE-START_DATE FROM T45)) B
WHERE L <= END_DATE - START_DATE
ORDER BY REGION;
ERROR:

ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"


I know connect by level is giving mutliple records, i am not able to link the query to the error suggested.
Re: Help in Query to achieve output? [message #663201 is a reply to message #663200] Thu, 25 May 2017 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You obviously did not read the link I posted.

Re: Help in Query to achieve output? [message #663210 is a reply to message #663201] Thu, 25 May 2017 14:05 Go to previous messageGo to next message
shawaj
Messages: 59
Registered: January 2016
Member
1 SELECT A.* FROM T45 A, (WITH DATA AS (SELECT START_DATE,END_DATE FROM T45)
2 SELECT DISTINCT START_DATE+LEVEL-1,START_DATE DATE2,END_DATE
3 FROM DATA
4 CONNECT BY LEVEL <=
5 END_DATE-START_DATE+1 ORDER BY 1) B
6 WHERE A.START_DATE=B.DATE2

[Updated on: Thu, 25 May 2017 14:08]

Report message to a moderator

Re: Help in Query to achieve output? [message #663213 is a reply to message #663210] Thu, 25 May 2017 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ This is wrong

2/ From your previous topic:
Michel Cadot wrote on Wed, 03 May 2017 21:32
Quote:
my code is formatted by toad but when i past here it removes all formatting ...i do not know why its happening
Just read How to use [code] tags and make your code easier to read.
3/ You should envisage to FIRST feedback in your topics.



Re: Help in Query to achieve output? [message #663245 is a reply to message #663213] Fri, 26 May 2017 18:08 Go to previous messageGo to next message
shawaj
Messages: 59
Registered: January 2016
Member
Thanks for reply Mr.Michel Cadot
Can you tell me what is the wrong with this query as you said "1/ This is wrong".
SELECT A.* FROM T45 A,
	(WITH DATA AS (SELECT START_DATE,END_DATE FROM T45)
 	SELECT DISTINCT START_DATE+LEVEL-1,START_DATE DATE2,END_DATE
 	FROM DATA
 	CONNECT BY LEVEL <= END_DATE-START_DATE+1 ORDER BY 1) B
 WHERE A.START_DATE=B.DATE2
Re: Help in Query to achieve output? [message #663251 is a reply to message #663245] Fri, 26 May 2017 21:54 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
SELECT REGION, DEPTNO, START_DATE+L-1 AS START_DATE FROM T45
INNER JOIN
(SELECT LEVEL AS L FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(END_DATE-START_DATE) +1 AS DT FROM T45)) B
ON END_DATE-START_DATE+1>=B.L
ORDER BY REGION, START_DATE;

Finally Achieved the output after going thru the post of connect by level suggested by Michel Cadot.
Thank You so much Michel Cadot Smile
Re: Help in Query to achieve output? [message #663274 is a reply to message #663251] Sat, 27 May 2017 08:46 Go to previous message
Solomon Yakobson
Messages: 2687
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.
Previous Topic: Create Year and week partition automatically
Next Topic: Using loop with a variable table name
Goto Forum:
  


Current Time: Sun Dec 17 11:29:50 CST 2017

Total time taken to generate the page: 0.02688 seconds