Need help with a sub query in Oracle Database.

I have attached a xls file showing the query with the result, can you please help with a sub query which will show results as I have shown - starting from line number 62?
ucp3_cluster_member_targets4.xls
Rao_SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
If you are using sqlplus you can get the empty columns with:
break on cluster_name
break on host_name
select ...

If not, you can use a row_number() trick with a case statement to either display the value or a null.

I'm not sure what you mean with the sub select.
0
Rao_SAuthor Commented:
Can you rewrite the script with the break clause for me?
By sub select, I meant sub query.
0
slightwv (䄆 Netminder) Commented:
Using sqlplus break, there is no clause.  It is those two commands followed by your select with no changes.

If you want the row_number method, give me a couple of minutes.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Here is a quick example using row_number.

I don't have your tables so cannot modify your query to test with.  It should just be adding the two row_number columns and wrapping it in the outer select like in my example.

--drop table tab1 purge;
create table tab1( PNC_FRAME char(4), CLUSTER_NAME char(7), HOST_NAME varchar2(40), DATABASE varchar2(40));

insert into tab1 values('UCP3','lods325','lods325dz.pncbank.com','oemdb94p_oemdb93p1');
insert into tab1 values('UCP3','lods325','lods325dz.pncbank.com','oemdb94p1');
insert into tab1 values('UCP3','lods325','lods326dz.pncbank.com','oemdb94p_oemdb93p2');
insert into tab1 values('UCP3','lods325','lods326dz.pncbank.com','oemdb94p');
insert into tab1 values('UCP3','lods325','lods327dz.pncbank.com','oemdb94p_oemdb93p3');
insert into tab1 values('UCP3','lods325','lods332dz.pncbank.com','sbxdb13p.pncbank.com_sbxdb13p_1');
insert into tab1 values('UCP3','lods325','lods332dz.pncbank.com	sbxdb13p.pncbank.com');
insert into tab1 values('UCP3','lods32b','lods32bdz.prod.pncint.net','ofddb07p.prod.pncint.net_ofddb07p2');
insert into tab1 values('UCP3','lods32b','lods32bdz.prod.pncint.net','blddb10p.prod.pncint.net_blddb10p2');
insert into tab1 values('UCP3','lods32b','lods32bdz.prod.pncint.net','ofddb07p.prod.pncint.net');
insert into tab1 values('UCP3','lods32b','lods32edz.prod.pncint.net','cdwdb07p.prod.pncint.net_cdwdb07p2');
insert into tab1 values('UCP3','lods32b','lods32edz.prod.pncint.net','blddb08p.prod.pncint.net_blddb08p2');
insert into tab1 values('UCP3','lods32b','lods32edz.prod.pncint.net','inmdb03p.prod.pncint.net_inmdb03p2');
insert into tab1 values('UCP3','lods32b','lods32edz.prod.pncint.net','cdwdb07p.prod.pncint.net');
commit;

select PNC_FRAME, case when cluster_rn = 1 then CLUSTER_NAME end cluster_name, case when host_rn=1 then HOST_NAME end hostnamae, DATABASE
from (
	select PNC_FRAME, CLUSTER_NAME, HOST_NAME, DATABASE,
		row_number() over(partition by pnc_frame, cluster_name order by pnc_frame, cluster_name, host_name, database) cluster_rn,
		row_number() over(partition by pnc_frame, cluster_name, host_name order by pnc_frame, cluster_name, host_name, database) host_rn
	from tab1
);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rao_SAuthor Commented:
hi slightwv,
Your example worked great!

In my case, I have several table that I need to pull data from, I have attached files with insert statement, to help create a multiple test table scenario that I need to pull data from.
Would you mind writing a similar query like above using the multiple tables I have?

Here is query for the test tables I have created. I need your help in rewriting this using the row_number() over(partition  ....   )

select c.property_value pnc_frame, a.target_name cluster_name, b.member_target_name host_name, d.target_name database_instance
from tab1_target a,tab2_member b,tab3_property c, (select target_name,host_name  from tab1_target where target_type in ('oracle_database') ) d
where A.TARGET_TYPE = B.AGGREGATE_TARGET_TYPE
and   A.TARGET_NAME = B.AGGREGATE_TARGET_NAME
and   A.TARGET_TYPE = C.TARGET_TYPE
and   A.TARGET_NAME = C.TARGET_NAME
and   A.TARGET_TYPE = 'cluster'
and   C.PROPERTY_NAME = 'udtp_8'
and   C.PROPERTY_VALUE = 'UCP3'
and   B.MEMBER_TARGET_TYPE = 'host'
and   d.host_name = b.member_target_name;
create_statements.txt
insert_into_tab1_target.sql
insert_into_tab2_member.sql
insert_into_tab3_property.sql
0
slightwv (䄆 Netminder) Commented:
I shouldn't need the sample data...

The select is the same no matter what the join is and how many tables are involved.

Just replace my columns with the correct columns in your select.

Something like this:
select c.property_value pnc_frame, a.target_name cluster_name, b.member_target_name host_name, d.target_name database_instance,
            row_number() over(partition by c.property_value, a.target_name order by c.property_value , a.target_name , host_name, d.target_name ) cluster_rn,
            row_number() over(partition by c.property_value, a.target_name , b.member_target_name order by c.property_value , a.target_name , b.member_target_name , d.target_name ) host_rn
 from tab1_target a,tab2_member b,tab3_property c, (select target_name,host_name  from tab1_target where
...

then wrap that in the same sort of outer select I used.
0
Rao_SAuthor Commented:
Everything worked correctly!
Thanks a ton!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.