Case statement in where clause is hanging!

[knmadhhu]

Hi Everybody

I am writing this statement,but is it hanging! Can anybody correct me? Any help will be appreciated!

select (cba.billing_na me1||' '||cba.billing_ name2) as cust_bill_name from USRP.cust_billi ng_acct cba,USRP.circui t ckt,USRP.custom er cust
where cba.MCN = case when ckt.CKT_MCN is NOT NULL THEN ckt.CKT_MCN ELSE ( select cust.MCN from USRP.customer cust where ckt.C_ID = cust.CUSTOMER_I D) end
AND cba.GRC = case when ckt.CKT_GRC IS NOT NULL THEN ckt.CKT_GRC ELSE (select cust.GRC from USRP.customer cust where ckt.C_ID = cust.CUSTOMER_I D) end
AND cba.SOC =case when ckt.CKT_SOC IS NOT NULL THEN ckt.CKT_SOC ELSE ( select cust.SOC from USRP.customer cust where ckt.C_ID = cust.CUSTOMER_I D) end

[amitpatel66]

Why you need to use a sub queries in your case when you can make things simpler:

[code=oracle]

select (cba.billing_na me1||' '||cba.billing_ name2) as cust_bill_name from USRP.cust_billi ng_acct cba,USRP.circui t ckt,USRP.custom er cust
where cba.MCN = case when ckt.CKT_MCN is NOT NULL THEN ckt.CKT_MCN ELSE cust.MCN end
AND cba.GRC = case when ckt.CKT_GRC IS NOT NULL THEN ckt.CKT_GRC ELSE cust.GRC end
AND cba.SOC =case when ckt.CKT_SOC IS NOT NULL THEN ckt.CKT_SOC ELSE cust.SOC end

[/code]

Try this and post back in case of any issues

[Pilgrim333]

Hi,

In the FROM clause the addition of the table USRP.customer cust is not needed, and gives you a cartesian product. If the table USRP.customer is populated with a lot of rows, then it can cause your query to hang.

Why don't you use the nvl function instead of the case statement?

Pilgrim.