We help IT Professionals succeed at work.
Get Started

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

dsjohndavid
dsjohndavid asked
on
519 Views
Last Modified: 2021-04-21
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:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE