Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query- Connect By (merged) (11g)
SQL Query- Connect By (merged) [message #684920] Tue, 28 September 2021 00:31 Go to next message
sayan13
Messages: 17
Registered: July 2013
Location: Pune
Junior Member
Hi,

I want to get the hierarchy of values(see comment below), but as per my query the results are returning as null

with table_a as
(
select 1 as seq, 'IP SC TOSAML:No port <> TOSA1>JKEL1_PDH:No port' as a, 'MCJ2G0109' as b,'TOSAML' as c,'9140227880213249394' as d,'Vlan 1' as e, 1 as rn, 3 as vlan_cnt from dual
UNION
select 1 as seq,'IP SC TOSAML:No port <> TOSA1>JKEL1_PDH:No port' as a, 'MCJ2G0109' as b,'TOSAML' as c,'9140227880213249394' as d,'Vlan 10' as e, 2 as rn, 3 as vlan_cnt from dual
UNION
select 1 as seq,'IP SC TOSAML:No port <> TOSA1>JKEL1_PDH:No port' as a, 'MCJ2G0109' as b,'TOSAML' as c,'9140227880213249394' as d,'Vlan 100' as e, 3 as rn, 3 as vlan_cnt from dual
)
select a,
b,
c,
rtrim(ltrim(sys_connect_by_path(e, ','), ','), ',') e, /*Results needed here as "vlan1, vlan10,vlan100" */
rn
from table_a
where rn = vlan_cnt
start with rn = 1
connect by prior rn = rn - 1
and prior c= c
group by seq, a, b, c, e, rn
order by a


Can anyone please assist how to do this?

Report message to a moderator

SQL Query- Connect By [message #684921 is a reply to message #684920] Tue, 28 September 2021 00:31 Go to previous messageGo to next message
sayan13
Messages: 17
Registered: July 2013
Location: Pune
Junior Member
Hi,

I want to get the hierarchy of values(see comment below), but as per my query the results are returning as null

with table_a as
(
select 1 as seq, 'IP SC TOSAML:No port <> TOSA1>JKEL1_PDH:No port' as a, 'MCJ2G0109' as b,'TOSAML' as c,'9140227880213249394' as d,'Vlan 1' as e, 1 as rn, 3 as vlan_cnt from dual
UNION
select 1 as seq,'IP SC TOSAML:No port <> TOSA1>JKEL1_PDH:No port' as a, 'MCJ2G0109' as b,'TOSAML' as c,'9140227880213249394' as d,'Vlan 10' as e, 2 as rn, 3 as vlan_cnt from dual
UNION
select 1 as seq,'IP SC TOSAML:No port <> TOSA1>JKEL1_PDH:No port' as a, 'MCJ2G0109' as b,'TOSAML' as c,'9140227880213249394' as d,'Vlan 100' as e, 3 as rn, 3 as vlan_cnt from dual
)
select a,
b,
c,
rtrim(ltrim(sys_connect_by_path(e, ','), ','), ',') e, /*Results needed here as "vlan1, vlan10,vlan100" */
rn
from table_a
where rn = vlan_cnt
start with rn = 1
connect by prior rn = rn - 1
and prior c= c
group by seq, a, b, c, e, rn
order by a


Can anyone please assist how to do this?

Report message to a moderator

Re: SQL Query- Connect By [message #684922 is a reply to message #684920] Tue, 28 September 2021 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Some posts extracted from your previous topic:

cookiemonster wrote on Wed, 24 July 2013 14:31
Please read and follow How to use [code] tags and make your code easier to read? and use it for displayed the results of queries. The above is unreadable.

What problem are you having writing this procedure?

Michel Cadot wrote on Wed, 24 July 2013 14:33
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

pablolee wrote on Wed, 24 July 2013 20:03
No sample data
No expected outputs
No logic explaining the expected output.

Analogue to your question

Here is two bags.
each bag contains balls.
The balls have colours and patterns
Compare the two bags and store the results in another bag.

Now sayan, tell me if you think you can provide a solution to what I have asked.

Report message to a moderator

Re: SQL Query- Connect By [message #684923 is a reply to message #684922] Tue, 28 September 2021 01:28 Go to previous message
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
(see comment below)
Which "comment"?

Report message to a moderator

Previous Topic: Extract string between 2 text
Next Topic: REGEXP_LIKE
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Mar 23 05:15:28 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.