0

I'm currently working on APEX where I expect the following query to work.. But its not working or giving me any errors.

here is the query

select et.equipment_type,
  max(case when name='Managed_Ip' then value end) as SNMP_port,
 e.equipment_name as equipment_name,
cu.customer_name as customer_name,
st.site_name as site_name
  
   from equipment_feature ef inner join equipments e on(e.equipment_id=ef.equipment_id) inner

join equipment_types et on (e.equipment_type_id=et.equipment_type_id)
inner join customers cu on(ef.name='customer_id'and  ef.value=cu.customer_id)
inner join site st on(ef.name='site_id' and ef.value=st.site_id)

group by et.equipment_type,e.equipment_name,cu.customer_name,st.site_name

At the same time if I comment some lines the query fetches the result.

select et.equipment_type,
  max(case when name='Managed_Ip' then value end) as SNMP_port,
 e.equipment_name as equipment_name,
--cu.customer_name as customer_name,
--st.site_name as site_name
  
   from equipment_feature ef inner join equipments e on(e.equipment_id=ef.equipment_id) inner

join equipment_types et on (e.equipment_type_id=et.equipment_type_id)
--inner join customers cu on(ef.name='customer_id' and  ef.value=cu.customer_id)
--inner join site st on(ef.name='site_id' and ef.value=st.site_id)


group by et.equipment_type,e.equipment_name
--,cu.customer_name,st.site_name

Any help would be really appreciated.

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by urtrivedi
0

Try following query

select et.equipment_type,
max(case when name='Managed_Ip' then value end) as SNMP_port,
e.equipment_name as equipment_name,
[B]cs.customer_name[/B]

from equipment_feature ef inner join equipments e on(e.equipment_id=ef.equipment_id) inner

join equipment_types et on (e.equipment_type_id=et.equipment_type_id)
inner join 
  [B](select 'customer_id' colname,customer_id,customer_name from customer
    union
    select 'site_id' colname,site_id as customer_id, site_name as customer_name from site)
 cs on(ef.name=cs.colname and ef.value=cs.customer_id)[/B]
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.