Home » SQL & PL/SQL » SQL & PL/SQL » Connect by limit issue (oracle 11g)
Connect by limit issue [message #594220] Mon, 26 August 2013 06:08 Go to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi all,

I tried below given query.

with emp as (
select 'hema' name, 3 id from dual union
select 'mohan', 4 from dual
)


select name
, id
from emp

connect by level <= id
and id = prior id
and prior dbms_random.value is not null
;

RESULT:

hema 3
hema 3
hema 3
mohan 4
mohan 4
mohan 4
mohan 4

I need these kind of result only.

But now i have changed the query like

with emp as (
select 'hema' name, 4 id from dual union
select 'mohan', 4 from dual
)


select name
, id
from emp

connect by level <= id
and id = prior id
and prior dbms_random.value is not null
;


Now it is giving irregular result.Kindly do needful.

I need

hema 3
hema 3
hema 3
hema 3
mohan 4
mohan 4
mohan 4
mohan 4
Re: Connect by limit issue [message #594221 is a reply to message #594220] Mon, 26 August 2013 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you understand your query?
Why "prior dbms_random.value is not null"? What does this mean?

What do you expect? What is your requirements (in words)? What are you trying to get (in words)?
Why do you want "hema 3" when you have "hema 4" in your data?

How can we know if the result for other data is correct or not?

Regards
Michel

[Updated on: Mon, 26 August 2013 06:18]

Report message to a moderator

Re: Connect by limit issue [message #594225 is a reply to message #594221] Mon, 26 August 2013 07:09 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Sorry cadot,

I need

hema 4
hema 4
hema 4
hema 4
mohan 4
mohan 4
mohan 4
mohan 4
Re: Connect by limit issue [message #594226 is a reply to message #594225] Mon, 26 August 2013 07:11 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
I need to print name based on id.
For example
id=7 then we need to print name 7 times.
Re: Connect by limit issue [message #594228 is a reply to message #594226] Mon, 26 August 2013 07:29 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The first query does that.

(it prints the name with id 3 three times, the name with id 4 four times, and it would print a name with id 7 seven times.)
Re: Connect by limit issue [message #594229 is a reply to message #594228] Mon, 26 August 2013 07:31 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
But if we give id as 7 for mohan and 7 for hema...It is giving irregular records...
Re: Connect by limit issue [message #594232 is a reply to message #594229] Mon, 26 August 2013 07:59 Go to previous messageGo to next message
Littlefoot
Messages: 19465
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does this improve your result?
SQL> with emp as (select 'hema' name, 4 id from dual
  2               union
  3               select 'mohan', 4 from dual)
  4      select name, id
  5        from emp
  6  connect by level <= id
  7    and id = prior id
  8    and name = prior name                          --> this line
  9    and prior dbms_random.value is not null;

NAME          ID
----- ----------
hema           4
hema           4
hema           4
hema           4
mohan          4
mohan          4
mohan          4
mohan          4

8 rows selected.

SQL>
Re: Connect by limit issue [message #594236 is a reply to message #594229] Mon, 26 August 2013 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The correct way(s) to do it is explained in row generator topic.

And if you don't understand a query that you find who knows where, do NOT use it.

Regards
Michel
Re: Connect by limit issue [message #594237 is a reply to message #594229] Mon, 26 August 2013 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
siraj.accet@gmail.com wrote on Mon, 26 August 2013 14:31
But if we give id as 7 for mohan and 7 for hema...It is giving irregular records...


Of course, this is the expected behaviour.

Regards
Michel

Re: Connect by limit issue [message #594250 is a reply to message #594229] Mon, 26 August 2013 12:35 Go to previous messageGo to next message
joy_division
Messages: 4502
Registered: February 2005
Location: East Coast USA
Senior Member
siraj.accet@gmail.com wrote on Mon, 26 August 2013 08:31
But if we give id as 7 for mohan and 7 for hema...It is giving irregular records...


What are "irregular" records?
Re: Connect by limit issue [message #594941 is a reply to message #594250] Wed, 04 September 2013 05:59 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi
Thank you littlefoot..I got the answer from your post..Sorry for late reply...Thanks again..
Re: Connect by limit issue [message #594942 is a reply to message #594941] Wed, 04 September 2013 06:00 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi littlefoot,
Can you explain your query?
Re: Connect by limit issue [message #594956 is a reply to message #594942] Wed, 04 September 2013 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you explain yours?

Regards
Michel
Re: Connect by limit issue [message #595024 is a reply to message #594956] Thu, 05 September 2013 02:44 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
I want to learn.So i am asking.If you want explain it.
Re: Connect by limit issue [message #595025 is a reply to message #595024] Thu, 05 September 2013 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But if you explain how you understand your query we can tell where you are not correct.
It is easier than to start to explain when you don't know where is the issue for you.

Regards
Michel
Re: Connect by limit issue [message #595032 is a reply to message #595025] Thu, 05 September 2013 04:20 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi cadot,
level <= id...Here we can limit levels based on id.
id = prior id...cant able to understand the diff between id & prior id..
and name = prior name
and prior dbms_random.value is not null ...This is random number.
Re: Connect by limit issue [message #595033 is a reply to message #595032] Thu, 05 September 2013 04:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2042
Registered: May 2013
Location: World Wide on the Web
Senior Member
If you confused with id and prior id as 2 different columns, then, be clear that prior is a keyword. It is to connect each id to its prior id to create the parent-child hierarchy. I think you need to go through the documentation and properly understand the hierarchichal queries.

Hierarchical Queries

[EDIT : Provided a link]

[Updated on: Thu, 05 September 2013 04:35]

Report message to a moderator

Re: Connect by limit issue [message #595084 is a reply to message #595033] Fri, 06 September 2013 07:41 Go to previous message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Thank you lalit
Previous Topic: Extracts subsets in the same query
Next Topic: select max date and Pk
Goto Forum:
  


Current Time: Wed Aug 20 02:05:47 CDT 2014

Total time taken to generate the page: 0.11858 seconds