Hi,
I am trying to optimize the below query code to get same output.
with cte as
(
select a.related_serial_number,
a.serial_number,
b.role_id,
c.role_name,
D.Customer_Name,
b.cmat_customer_id,
b.CMAT_site_id,
f.system_status,
f.orgnl_sales_order_number,
a.creation_date,
a.instance_relationship_id
from eim_instance_relationships a,
eim.EIM_LATEST_SYS_PARTY_ROLE b,
eim.EIM_Roles c,
eim.EIM_Party D,
EIM.EIM_System f
where a.Pkey between '31' and '45'
and a.p_pkey between '1' and '2'
and A.serial_number=b.system_serial_number
and D.cmat_customer_id=b.cmat_customer_id
and A.serial_number=f.system_serial_number
and b.role_id=c.role_id
and related_serial_number='01c33679-a5b2-11e4-82f6-123478563412'
and b.role_id in ('1','4','10','3','19')
and a.related_component_type='CLUSTER'
and a.component_type='CONTROLLER'
and a.related_serial_number in
(
select related_serial_number from eim.eim_instance_relationships e
where e.related_component_type='CLUSTER'
and e.component_type='CONTROLLER'
and e.p_pkey between '1' and '2'
and e.Pkey between '31' and '45'
group by e.related_serial_number
having count(*)>1
) order by 1,2
)
,cte2 as
(
select
row_number() over (partition by role_name,cmat_site_id order by instance_relationship_id desc) R,instance_relationship_id,
related_serial_number,serial_number,role_id,role_name,Customer_Name,cmat_customer_id,CMAT_site_id,system_status,orgnl_sales_order_number,creation_date
from cte
where (related_serial_number,role_name) in
(
select related_serial_number,role_name from
(
select related_serial_number,role_name,count(*) from
(
select related_serial_number,serial_number,role_id,role_name,customer_name,CMAT_Customer_ID,cmat_site_id,system_status,orgnl_sales_order_number,count(*) from cte
group by related_serial_number,serial_number,role_id,role_name,customer_name,CMAT_Customer_ID,cmat_site_id,system_status,orgnl_sales_order_number
)
group by related_serial_number,role_name
HAVING COUNT(distinct CMAT_Site_ID)>1
)
)
)
select * from cte2
where r=1 order by related_serial_number,serial_number