Home » SQL & PL/SQL » SQL & PL/SQL » group by... ORA-22818 subquery expression not allowed here"
group by... ORA-22818 subquery expression not allowed here" [message #242721] Mon, 04 June 2007 18:21 Go to next message
russki
Messages: 14
Registered: May 2007
Junior Member
I have this script but now i want to do a SUM(a.gl_amt)...

when i do that i have to do a group by ...this is an error i get when i insert CASE into GROUP BY
"ORA-22818 subquery expression not allowed here"

SELECT b.en_ent
      ,e.sa_sub
      ,c.cc_cstctr
      ,d.acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type
      ,CASE
         WHEN a.gl_tr_type = 'SO' THEN
          (SELECT g. cust_name
           FROM  
                  finmart.D_CUSTOMER      g
                 ,dssmart.f_sales_invoice h
           WHERE  a.gl_doc = h.inv_nbr
           AND    h.inv_cust_bill_to_nbr = g.cust_nbr)
         ELSE
          'N/A'
       END cust_name
      ,a.gl_amt
FROM   F_ACCT_TRX_HIST_STG2 a
      ,D_ENTITY_STG2        b
      ,D_COSTCTR_STG2       c
      ,D_ACCTS_STG2         d
      ,D_SUBACCTS_STG2      e
      ,D_PERIOD_STG1        f
WHERE  a.gl_ent = b.en_ent
AND    c.cc_cstctr = UPPER(a.gl_cc)
AND    d.acct_acc = a.gl_acc
AND    e.sa_sub = a.gl_sa
AND    a.gl_eff_dt = f.calendar_date 
Re: group by... ORA-22818 subquery expression not allowed here" [message #242751 is a reply to message #242721] Tue, 05 June 2007 00:11 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In that case, use your "main" query as an inline view, such as
SELECT x.en_ent, x.sa_sub, ..., sum(x.gl_amt) sum_amt
FROM 
-- your current query starts here ------------------------
     (SELECT b.en_ent, e.sa_sub, ...
             CASE WHEN ... THEN
                    (SELECT g.cust_name 
                     FROM ...
             END cust_name
      FROM F_ACCT_TRX_HIST_STG2 a,
           D_ENTITY_STG2        b, ...
      WHERE ...
-- your current query ends here --------------------------
     ) x
GROUP BY x.en_ent, x.sa_sub, ...;
Re: group by... ORA-22818 subquery expression not allowed here" [message #242955 is a reply to message #242751] Tue, 05 June 2007 10:54 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
I did as you said,

I'm using TOAD to test the query,,,,,takes too long and than TOAD just freezes up!

Why is this happening as soon as I'm doing SUM and then GROUP BY, what should i do?

SELECT x. en_ent
      ,x. sa_sub
      ,x. cc_cstctr
      ,x. acct_acc
      ,x. en_entity_lng
      ,x. sa_sub_lng
      ,x. cost_ctr_lng
      ,x. acct_acc_lng
	  ,x. cust_name
      ,x. fiscal_month
      ,x. fiscal_year_lng
      ,x. acct_type
      ,SUM(x.gl_amt) 
FROM 
    (SELECT b.en_ent
      ,e. sa_sub
      ,c. cc_cstctr
      ,d. acct_acc
      ,b. en_entity_lng
      ,e. sa_sub_lng
      ,c. cost_ctr_lng
      ,d. acct_acc_lng
      ,f. fiscal_month
      ,f. fiscal_year_lng
      ,d. acct_type
      ,a. gl_tr_type
      ,CASE
         WHEN a.gl_tr_type = 'SO' THEN
          (SELECT g. cust_name
           FROM  
                  finmart.D_CUSTOMER      g
                 ,dssmart.f_sales_invoice h
           WHERE  a.gl_doc = h.inv_nbr
           AND    h.inv_cust_bill_to_nbr = g.cust_nbr)
         ELSE
          'N/A'
       END cust_name
      ,a.gl_amt
FROM   F_ACCT_TRX_HIST_STG2 a
      ,D_ENTITY_STG2        b
      ,D_COSTCTR_STG2       c
      ,D_ACCTS_STG2         d
      ,D_SUBACCTS_STG2      e
      ,D_PERIOD_STG1        f
WHERE  a.gl_ent = b.en_ent
AND    c.cc_cstctr = UPPER(a.gl_cc)
AND    d.acct_acc = a.gl_acc
AND    e.sa_sub = a.gl_sa
AND    a.gl_eff_dt = f.calendar_date) x
GROUP BY  x. en_ent
         ,x. sa_sub
         ,x. cc_cstctr
         ,x. acct_acc
         ,x. en_entity_lng
         ,x. sa_sub_lng
         ,x. cost_ctr_lng
         ,x. acct_acc_lng
	     ,x. cust_name
         ,x. fiscal_month
         ,x. fiscal_year_lng
         ,x. acct_type 

Re: group by... ORA-22818 subquery expression not allowed here" [message #242995 is a reply to message #242955] Tue, 05 June 2007 12:33 Go to previous message
concorde800
Messages: 52
Registered: May 2007
Member
A few pointers:-

IF your tables have large amount of rows, try using parallel hints for your SELECT statements. Something like /*+ parallel (a,6) */ where a is your table name.

Use EXPLAIN PLAN on your queries and try to get costs on the SQL statement. Pick the one with lower cost.

Make sure your tables have updated STATS.

If your tables are large, make sure they are INDEXED for faster query processing.

Previous Topic: Key preserved
Next Topic: Catch Exceptions
Goto Forum:
  


Current Time: Thu Dec 08 04:04:42 CST 2016

Total time taken to generate the page: 0.16146 seconds