We help IT Professionals succeed at work.

Can a scalar subquery in the SELECT clause be used in the GROUP BY clause?

Can a scalar subquery in the SELECT clause be used in the GROUP BY clause?

The query below gets an Oracle ORA-00904 "BC.BILL_CYCLE" invalid identifier error.

SELECT ca.comp_id,
       ip.invoice_plan_id,
       revenue_code,
       (CASE WHEN variable_type = 'N' THEN 'Volume'
             WHEN variable_type = 'C' THEN 'Revenue'
        END) AS variable_type,
       curr.currency_desc,
       (SELECT bill_cycle
        FROM prod p, bill_variable bv
        WHERE bd.variable_name = bv.variable_name
          AND bv.prod_id = p.prod_id) bc,
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-13),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-13",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-12),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-12",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-11),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-11",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-10),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-10",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-9),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-9",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-8),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-8",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-7),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-7",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-6),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-6",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-5),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-5",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-4),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-4",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-3),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-3",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-2),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-2",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-1),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle-1",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,-0),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Current",
       Sum(Decode(bd.bill_cycle_month,To_Char(Add_Months(SYSDATE,+1),'YYYYMM'),
       (CASE WHEN credit_debit_code = 'D' THEN value * -1.0 ELSE value END),0)) "Bill Cycle+1"
FROM billable_data bd, invoice_plan ip, currency curr, comp_alias ca
WHERE bd.comp_id = ca.alias_comp_id
  AND variable_type IN ('C','N')
  AND bd.bill_cycle_month > to_char(ADD_MONTHS(SYSDATE,-14), 'YYYYMM')
  AND value > 0
  AND bd.rec_type IN ('0','1','2')
  AND revenue_code NOT IN ('000','009','303','338','345',
                           '346','399','887','888','991')
  AND bd.invoice_plan_id = ip.invoice_plan_id
  AND ip.currency_id = curr.currency_id
GROUP BY ca.comp_id, revenue_code, variable_type, ip.invoice_plan_id, curr.currency_desc, bc.bill_cycle
ORDER BY ca.comp_id ASC, revenue_code
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
not directly, but you don't need to.  group by the determining columns of the sub query, in addition to the other columns

in this case...


GROUP BY ca.comp_id, revenue_code, variable_type, ip.invoice_plan_id, curr.currency_desc, bc.bill_cycle, bd.variable_name
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
you could also do the lookup externally to the query by putting the rest of it in an inline view
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.