Row generator [message #624631] |
Wed, 24 September 2014 05:59 |
|
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
Hi,
I have to print the data from employee table for 4 days of calendar month between 26/09/2014 and 31/06/2014 or it can be for any date range in a month for all the employees .i have written the query using row generator technique but its getting me wrong results.
CREATE TABLE EMP_MAST(E_CODE VARCHAR2(12));
INSERT INTO EMP_MAST(E_CODE) VALUES ('AA');
INSERT INTO EMP_MAST(E_CODE) VALUES ('BB');
INSERT INTO EMP_MAST(E_CODE) VALUES ('CC');
select E_CODE,DATE '2014-09-25' + LEVEL DT
FROM EMP_MAST ,DUAL
CONNECT BY LEVEL <= 5 ;
--its getting me 363 records,whereas it should be 5 like below
-- how can i achieve the following results
E_CODE DT
AA 9/26/2014
AA 9/27/2014
AA 9/28/2014
AA 9/29/2014
AA 9/30/2014
BB 9/26/2014
BB 9/27/2014
BB 9/28/2014
BB 9/29/2014
BB 9/30/2014
CC 9/26/2014
CC 9/27/2014
CC 9/28/2014
CC 9/29/2014
CC 9/30/2014
Edited by Lalit : Fixed code tags
[Updated on: Wed, 24 September 2014 06:22] by Moderator Report message to a moderator
|
|
|
|
Re: Row generator [message #624648 is a reply to message #624632] |
Wed, 24 September 2014 07:48 |
|
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
Thanks Michel , very resourceful link and i managed to resolve the query with that link,but please explain me that model query as i am trying to understand it.
SELECT e_code, dt
FROM emp_mast,
(SELECT DATE '2014-09-25' + LEVEL dt
FROM DUAL
CONNECT BY LEVEL <= 5)
ORDER BY e_code, dt
*BlackSwan corrected {code} tags
[Updated on: Wed, 24 September 2014 08:13] by Moderator Report message to a moderator
|
|
|
|
Re: Row generator [message #624695 is a reply to message #624651] |
Wed, 24 September 2014 12:42 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The inner query generates the dates between 26-sep-2014 and 30-sep-2014 and then does a Cartesian join with the e_code in the emp_mast table.
|
|
|
|
|
|
|
|
|