Home » SQL & PL/SQL » SQL & PL/SQL » nested subqueries
nested subqueries [message #242095] Thu, 31 May 2007 10:51 Go to next message
russki
Messages: 14
Registered: May 2007
Junior Member
hello,
I'm trying to write a subquery (1st time) in my WHERE clause.
the way i wanted to work is. If gl_tr_type = 'SO' then execute the rest. But then i need g.cust_name to be in the group by between d. ACCT_ACC_LNG , f.FISCAL_MONTH,?

Thank-you for your Help!!!
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,
SUM(a.gl_amt)

FROM
F_ACCT_TRX_HIST_STG1 a,
D_ENTITY_STG2 b,
D_COSTCTR_STG2 c,
D_ACCTS_STG2 d,
D_SUBACCTS_STG2 e,
D_PERIOD_STG1 f

WHERE a.gl_eff_dt BETWEEN '02-april-2007' AND '02-april-2007' AND
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 AND

(SELECT g. cust_name


FROM FINMART.D_CUSTOMER g,
DSSMART.F_SALES_INVOICE h

WHERE a.gl_tr_type = 'SO'AND
a.gl_doc =h.inv_nbr AND
h.inv_cust_bill_to_nbr = g.cust_nbr)




GROUP BY 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
Re: nested subqueries [message #242098 is a reply to message #242095] Thu, 31 May 2007 11:18 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
russki wrote on Thu, 31 May 2007 11:51

WHERE a.gl_eff_dt BETWEEN '02-april-2007' AND '02-april-2007' AND



This is invalid syntax for a query if a.gl_eff_dt is a date. If it is a VARCHAR2, then you would be ok, but it will only return values where the character string='02-april-2007'

You need to use a TO_DATE function on character strings if you want to compare a literal or VARCHAR2 variable to a DATE column.
Re: nested subqueries [message #242099 is a reply to message #242098] Thu, 31 May 2007 11:22 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
Joy,

the date part is working, i need help with subquerie!

thank-you
Re: nested subqueries [message #242115 is a reply to message #242099] Thu, 31 May 2007 12:50 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
It may be working in YOUR exact case, but it is incorrect. Better to use proper syntax and programming method from the start before you get into a bad habit and get bitten.
FOO SCOTT> create table  F_ACCT_TRX_HIST_STG1 (gl_eff_dt date);

Table created.

FOO SCOTT>alter session set nls_date_format='MM/DD/YYYY'
  2  /

Session altered.

FOO SCOTT>select * from F_ACCT_TRX_HIST_STG1 a
  2  where a.gl_eff_dt BETWEEN '02-april-2007' AND '02-april-2007';
where a.gl_eff_dt BETWEEN '02-april-2007' AND '02-april-2007'
                          *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


What if someone is connecting through a client that has a different NLS_DATE_FORMAT than you?

[Updated on: Thu, 31 May 2007 12:54]

Report message to a moderator

Re: nested subqueries [message #242127 is a reply to message #242115] Thu, 31 May 2007 13:51 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
Guys thank-you but i'm having problems not with a date, that just for a test.

Im having problems with
I'm trying to write a subquery (1st time) in my WHERE clause.
the way i wanted to work is. If gl_tr_type = 'SO' then execute the rest. But then i need g.cust_name to be in the group by between d. ACCT_ACC_LNG , f.FISCAL_MONTH,?


thank-you!!!
Re: nested subqueries [message #242136 is a reply to message #242127] Thu, 31 May 2007 14:34 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Formatted your code a bit (you're welcome, next time try to use format tags):
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
      ,SUM(a.gl_amt)
FROM   f_acct_trx_hist_stg1 a
      ,d_entity_stg2        b
      ,d_costctr_stg2       c
      ,d_accts_stg2         d
      ,d_subaccts_stg2      e
      ,d_period_stg1        f
WHERE  a.gl_eff_dt BETWEEN to_date('02-apr-2007'
                                  ,'dd-mon-yyyy') AND
       to_date('02-apr-2007'
              ,'dd-mon-yyyy')
AND    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
AND    (SELECT g. cust_name
        FROM   finmart.d_customer      g
              ,dssmart.f_sales_invoice h
        WHERE  a.gl_tr_type = 'SO'
        AND    a.gl_doc = h.inv_nbr
        AND    h.inv_cust_bill_to_nbr = g.cust_nbr)
GROUP  BY 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


What you're basically doing here is:

select ...
from   ...
where  ...
and    (select ...
        from   ...
        where  ...)


It doesn't work this way. Because a where clause is supposed to evaluate something to true or false (or limit data or whatever). But here it's like "AND (select something)". What you need to do is: "AND (select something) = <something>".

Example:
SELECT e.last_name
FROM   employees e
WHERE  e.emp_id > 100
AND    e.hire_date > to_date('01-04-2005'
                            ,'dd-mm-yyyy')
AND    e.dept_id = (SELECT d.dept_id
                    FROM   dept d
                    WHERE  d.dept_name = 'Accounting')


Or, if you mean to select something is some value is 'SO' and else leave that result empty, then a wild guess would be that you mean:

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
      ,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
          NULL
       END cust_name
      ,SUM(a.gl_amt)
FROM   f_acct_trx_hist_stg1 a
      ,d_entity_stg2        b
      ,d_costctr_stg2       c
      ,d_accts_stg2         d
      ,d_subaccts_stg2      e
      ,d_period_stg1        f
WHERE  a.gl_eff_dt BETWEEN '02-april-2007' AND '02-april-2007'
AND    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 AND
GROUP  BY 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 .
         ,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
             NULL
          END


If you want tested code, please post table definition and insert scripts.
Re: nested subqueries [message #242151 is a reply to message #242136] Thu, 31 May 2007 15:32 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
Wow 1st of all thank you so much,,,,you are the 1st person in 3 days that understood exactly what i need.

My question is, the case statment in a group by,,

When Im running the script i get an error in Group By (Select

it says:ORA-22818 subquery expression not allowed here........?




again thank-you very much for helping................
Re: nested subqueries [message #242152 is a reply to message #242151] Thu, 31 May 2007 15:35 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
oh and one more thing,,,,,,,,,,,

f. fiscal_year_lng
,d. acct_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
NULL
END cust_name
,SUM(a.gl_amt)

Do i need to add f_acct_trx_hist_stg1 a, into FROM ? for a.gl_doc?
Re: nested subqueries [message #242160 is a reply to message #242151] Thu, 31 May 2007 16:25 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
it works but only when i Delete CASE from GROUP BY!? why it's not reading in GROUP BY?

thank-you
Re: nested subqueries [message #242345 is a reply to message #242136] Fri, 01 June 2007 11:10 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
skooman,

i'm trying to fix it, and i'm not getting any results?
keep getting "ORA-22818 subquery expression not allowed here"

thank-you for your help

Re: nested subqueries [message #243008 is a reply to message #242345] Tue, 05 June 2007 13:24 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Oops, you're right, subselects are not allowed in the group by (see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions010.htm#i1033549).

Solution is to first do the case-stuff and than around that group the result. I've made an example on emp/dept: let's say we want the sum of salaries per job_id and if the last name starts with a G then we want department name shown (ridiculous example, but had to make something up):

SQL> SELECT sub.job_id
  2        ,sub.case_result
  3        ,SUM(sub.salary)
  4  FROM   (SELECT e.job_id
  5                ,CASE
  6                   WHEN substr(e.last_name
  7                              ,1
  8                              ,1) = 'G' THEN
  9                    (SELECT d.department_name
 10                     FROM   departments d
 11                     WHERE  d.department_id = e.department_id)
 12                 END case_result
 13                ,e.salary
 14          FROM   employees e) sub
 15  GROUP  BY sub.job_id
 16           ,sub.case_result
 17  /

JOB_ID     CASE_RESULT                    SUM(SUB.SALARY)
---------- ------------------------------ ---------------
SH_CLERK                                            56000
AD_ASST                                              4400
HR_REP                                               6500
AC_MGR                                              12000
AD_PRES                                             24000
ST_CLERK                                            53300
PR_REP                                              10000
SA_MAN                                              61000
SA_REP     Sales                                     9500
IT_PROG                                             28800
PU_MAN                                              11000
SA_REP                                             241000
AC_ACCOUNT Accounting                                8300
AD_VP                                               34000
FI_ACCOUNT                                          39600
PU_CLERK                                            13900
ST_CLERK   Shipping                                  2400
MK_MAN                                              13000
MK_REP                                               6000
FI_MGR     Finance                                  12000
ST_MAN                                              36400
SH_CLERK   Shipping                                  8300

22 rows selected


Edit:
Now I see that someone else already offered the same solution in another thread and that you ran into an performance issue. Like stated in the other thread: Are the stats up to date? If so, post explain plan here and we'll try to see if we can help.

[Updated on: Tue, 05 June 2007 13:28]

Report message to a moderator

Re: nested subqueries [message #243011 is a reply to message #242095] Tue, 05 June 2007 13:26 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
I have an off the wall idea/suggestion.
What happens if you create a view which includes the CASE & do desired SELECT off this new view?
Previous Topic: Catch Exceptions
Next Topic: How to save resultset of procedure into a table ?
Goto Forum:
  


Current Time: Wed Dec 07 04:41:01 CST 2016

Total time taken to generate the page: 0.07657 seconds