I want to create sub query in stored proc in oracle the following query giving error too many values

Question

I want to add this query in stored procedure but it gives an error: ORA-00913: too many values

Select SUM(CONNMASS.CONN_BILLAMOUNT) as Revenuebilled, 
       count(CONNMASS.CONN_BILLEDUNITS) AS volumebilled,
       count(MASSBILL.BM_lo_id) as normalbilled,
       ( SELECT COUNT(CASE WHEN SRM_DISCON_STATUS_ID = 3 THEN 1 END) AS ACTIVE_CONNECTIONS,
                COUNT(CASE WHEN SRM_DISCON_STATUS_ID = 2 THEN 1 END) AS DISC_CONNECTIONS
         from Connection_master)
from   CONNECTION_MASTER CONNMASS
       left join BILLING_MASTER MASSBILL
       on MASSBILL.CONN_SERVICE_NO = CONNMASS.CONN_SERVICE_NO;

Answer 1

The problem is that you are selecting multiple values in the sub-query. You can only select a single value in a sub-query in the SELECT clause.

However, you do not need the sub-query and appear to want:

Select SUM(CONNMASS.CONN_BILLAMOUNT) as Revenuebilled, 
       count(CONNMASS.CONN_BILLEDUNITS) AS volumebilled,
       count(MASSBILL.BM_lo_id) as normalbilled,
       COUNT(CASE SRM_DISCON_STATUS_ID WHEN 3 THEN 1 END) AS ACTIVE_CONNECTIONS,
       COUNT(CASE SRM_DISCON_STATUS_ID WHEN 2 THEN 1 END) AS DISC_CONNECTIONS
from   CONNECTION_MASTER CONNMASS
       left join BILLING_MASTER MASSBILL
       on MASSBILL.CONN_SERVICE_NO = CONNMASS.CONN_SERVICE_NO;

If you did want the sub-queries then:

Select SUM(CONNMASS.CONN_BILLAMOUNT) as Revenuebilled, 
       count(CONNMASS.CONN_BILLEDUNITS) AS volumebilled,
       count(MASSBILL.BM_lo_id) as normalbilled,
       ( SELECT COUNT(CASE SRM_DISCON_STATUS_ID WHEN 3 THEN 1 END)
         from Connection_master) AS ACTIVE_CONNECTIONS,
       ( SELECT COUNT(CASE SRM_DISCON_STATUS_ID WHEN 2 THEN 1 END)
         from Connection_master) AS DISC_CONNECTIONS
from   CONNECTION_MASTER CONNMASS
       left join BILLING_MASTER MASSBILL
       on MASSBILL.CONN_SERVICE_NO = CONNMASS.CONN_SERVICE_NO;