Home » SQL & PL/SQL » SQL & PL/SQL » Row generator (Oracle 10.2)
Row generator [message #624631] Wed, 24 September 2014 05:59 Go to next message
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 #624632 is a reply to message #624631] Wed, 24 September 2014 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/95011/483276/#msg_483276

Re: Row generator [message #624648 is a reply to message #624632] Wed, 24 September 2014 07:48 Go to previous messageGo to next message
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 #624651 is a reply to message #624648] Wed, 24 September 2014 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Sorry but what don't you understand?

Re: Row generator [message #624695 is a reply to message #624651] Wed, 24 September 2014 12:42 Go to previous messageGo to next message
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.
Re: Row generator [message #624696 is a reply to message #624695] Wed, 24 September 2014 13:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Bill, OP is probably asking about the MODEL clause in the row generator thread.
Re: Row generator [message #624697 is a reply to message #624696] Wed, 24 September 2014 13:38 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
oops...lol
Re: Row generator [message #624699 is a reply to message #624696] Wed, 24 September 2014 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I don't think he spoke about MODEL clause but about "model query" as "model of query" that is "kind of query".

[Updated on: Wed, 24 September 2014 14:01]

Report message to a moderator

Re: Row generator [message #624712 is a reply to message #624699] Wed, 24 September 2014 22:57 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
yes michel i was referring to Model clause only, i didnt understand the rules part mentioned in that, dimension are number of dimension like one or two , measures are values on which computations will be done.I would appreciate if someone can help me by writing the same requirement of mine using model clause.It will just be a jumpstart for me to learn this powerful model clause.
Re: Row generator [message #624723 is a reply to message #624712] Thu, 25 September 2014 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Model clause can't be explained in a forum post, it is very complex.
If you want to know it, study it in the documentation, then read in forum the cases where it is used.

Re: Row generator [message #624729 is a reply to message #624723] Thu, 25 September 2014 01:17 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Michel , i will read that documentation.
Previous Topic: Questing regarding level and connect by
Next Topic: Help on a query
Goto Forum:
  


Current Time: Sun Jul 13 09:40:09 CDT 2025