Home » SQL & PL/SQL » SQL & PL/SQL » nested subqueries
| nested subqueries [message #242095] |
Thu, 31 May 2007 10:51  |
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  |
joy_division
Messages: 4963
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 #242115 is a reply to message #242099] |
Thu, 31 May 2007 12:50 |
joy_division
Messages: 4963
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 |
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 |
skooman
Messages: 913
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 |
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 |
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 #242345 is a reply to message #242136] |
Fri, 01 June 2007 11:10 |
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 |
skooman
Messages: 913
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 |
 |
BlackSwan
Messages: 26766
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?
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 09:13:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
