Home » SQL & PL/SQL » SQL & PL/SQL » problem with clause WITH (subquery_factoring_clause)
| problem with clause WITH (subquery_factoring_clause) [message #33323] |
Mon, 04 October 2004 03:05  |
Olga
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
I have problem with standart example (Oracle9i SQL Reference) :
WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;
My problem consist in that query gave back 0 rows, though
select sum(dept_total)/COUNT(*)
from (
SELECT department_name, SUM(salary) dept_total
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY department_name) dept_costs
gave back 1 row (that's correct).
How can I decide my problem with subquery_factoring_clause?
|
|
|
|
|
|
| Re: problem with clause WITH (subquery_factoring_clause) [message #33342 is a reply to message #33337] |
Mon, 04 October 2004 22:48  |
Olga
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
Here is data in my tables:
EMPLOYEES:
-------------
100 Steven King SKING 515.123.4567 17.06.1987 AD_PRES 24000,00 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21.09.1989 AD_VP 17000,00 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13.01.1993 AD_VP 17000,00 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03.01.1990 IT_PROG 9000,00 102 60
104 Bruce Ernst BERNST 590.423.4568 21.05.1991 IT_PROG 6000,00 103 60
105 David Austin DAUSTIN 590.423.4569 25.06.1997 IT_PROG 4800,00 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05.02.1998 IT_PROG 4800,00 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07.02.1999 IT_PROG 4200,00 103 60
DEPARTMENTS:
-------------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
SELECT department_name, SUM(salary) dept_total
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY department_name
returned
Accounting 20300
Administration 4400
Executive 58000
Finance 51600
Human Resources 6500
IT 28800
Marketing 19000
Public Relations 10000
Purchasing 24900
Sales 304500
Shipping 156400
The query
select SUM(dept_total)/COUNT(*) avg
from
(SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name) dept_costs
was returned one row:
avg
--------------------
62218,1818181818
It is average value by total salary in all departments.
BUT:
WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;
do no work.
The query from in-line view gave back NULL, in my mind.
Is anything wrong?
|
|
|
|
| Re: problem with clause WITH (subquery_factoring_clause) [message #33344 is a reply to message #33342] |
Tue, 05 October 2004 01:33 |
 |
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The data that you have posted does not match the query results that you have posted. For example, your employees table only shows employees from two departments (60 IT and 90 Executive), but your query results show multiple departments. How did you obtain the table data? Is it possible that you are selecting from two different schemas? You have prefaced your table names with the hr schema in some of your queries, but not in others. Are you running the queries from the hr schema or from some other schema that may have similar tables or synonyms? What do you get when you run all of them from the hr schema? When I run your queries with your data, I get the expected results, as demonstrated below. I loaded the data into tables in the scott schema and ran the queries from the scott schema without prefacing the table names with schema names.
scott@ORA92> WITH
2 dept_costs AS
3 (SELECT department_name, SUM(salary) dept_total
4 FROM employees e, departments d
5 WHERE e.department_id = d.department_id
6 GROUP BY department_name),
7 avg_cost AS
8 (SELECT SUM (dept_total) / COUNT(*) avg
9 FROM dept_costs)
10 SELECT *
11 FROM dept_costs
12 WHERE dept_total >
13 (SELECT avg
14 FROM avg_cost)
15 ORDER BY department_name
16 /
DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Executive 58000
scott@ORA92> SELECT SUM (dept_total) / COUNT(*)
2 FROM (SELECT department_name, SUM(salary) dept_total
3 FROM employees e, departments d
4 WHERE e.department_id = d.department_id
5 GROUP BY department_name) dept_costs
6 /
SUM(DEPT_TOTAL)/COUNT(*)
------------------------
43400
|
|
|
|
| Re: problem with clause WITH (subquery_factoring_clause) [message #33346 is a reply to message #33344] |
Tue, 05 October 2004 02:39 |
Olga
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
sory, here is full data from table EMPLOYEES (107 rows)
100 Steven King SKING 515.123.4567 17.06.1987 AD_PRES 24000,00 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21.09.1989 AD_VP 17000,00 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13.01.1993 AD_VP 17000,00 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03.01.1990 IT_PROG 9000,00 102 60
104 Bruce Ernst BERNST 590.423.4568 21.05.1991 IT_PROG 6000,00 103 60
105 David Austin DAUSTIN 590.423.4569 25.06.1997 IT_PROG 4800,00 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05.02.1998 IT_PROG 4800,00 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07.02.1999 IT_PROG 4200,00 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17.08.1994 FI_MGR 12000,00 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16.08.1994 FI_ACCOUNT 9000,00 108 100
110 John Chen JCHEN 515.124.4269 28.09.1997 FI_ACCOUNT 8200,00 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30.09.1997 FI_ACCOUNT 7700,00 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07.03.1998 FI_ACCOUNT 7800,00 108 100
113 Luis Popp LPOPP 515.124.4567 07.12.1999 FI_ACCOUNT 6900,00 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 07.12.1994 PU_MAN 11000,00 100 30
115 Alexander Khoo AKHOO 515.127.4562 18.05.1995 PU_CLERK 3100,00 114 30
116 Shelli Baida SBAIDA 515.127.4563 24.12.1997 PU_CLERK 2900,00 114 30
117 Sigal Tobias STOBIAS 515.127.4564 24.07.1997 PU_CLERK 2800,00 114 30
118 Guy Himuro GHIMURO 515.127.4565 15.11.1998 PU_CLERK 2600,00 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10.08.1999 PU_CLERK 2500,00 114 30
120 Matthew Weiss MWEISS 650.123.1234 18.07.1996 ST_MAN 8000,00 100 50
121 Adam Fripp AFRIPP 650.123.2234 10.04.1997 ST_MAN 8200,00 100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 01.05.1995 ST_MAN 7900,00 100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10.10.1997 ST_MAN 6500,00 100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16.11.1999 ST_MAN 5800,00 100 50
125 Julia Nayer JNAYER 650.124.1214 16.07.1997 ST_CLERK 3200,00 120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28.09.1998 ST_CLERK 2700,00 120 50
127 James Landry JLANDRY 650.124.1334 14.01.1999 ST_CLERK 2400,00 120 50
128 Steven Markle SMARKLE 650.124.1434 08.03.2000 ST_CLERK 2200,00 120 50
129 Laura Bissot LBISSOT 650.124.5234 20.08.1997 ST_CLERK 3300,00 121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30.10.1997 ST_CLERK 2800,00 121 50
131 James Marlow JAMRLOW 650.124.7234 16.02.1997 ST_CLERK 2500,00 121 50
132 TJ Olson TJOLSON 650.124.8234 10.04.1999 ST_CLERK 2100,00 121 50
133 Jason Mallin JMALLIN 650.127.1934 14.06.1996 ST_CLERK 3300,00 122 50
134 Michael Rogers MROGERS 650.127.1834 26.08.1998 ST_CLERK 2900,00 122 50
135 Ki Gee KGEE 650.127.1734 12.12.1999 ST_CLERK 2400,00 122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 06.02.2000 ST_CLERK 2200,00 122 50
137 Renske Ladwig RLADWIG 650.121.1234 14.07.1995 ST_CLERK 3600,00 123 50
138 Stephen Stiles SSTILES 650.121.2034 26.10.1997 ST_CLERK 3200,00 123 50
139 John Seo JSEO 650.121.2019 12.02.1998 ST_CLERK 2700,00 123 50
140 Joshua Patel JPATEL 650.121.1834 06.04.1998 ST_CLERK 2500,00 123 50
141 Trenna Rajs TRAJS 650.121.8009 17.10.1995 ST_CLERK 3500,00 124 50
142 Curtis Davies CDAVIES 650.121.2994 29.01.1997 ST_CLERK 3100,00 124 50
143 Randall Matos RMATOS 650.121.2874 15.03.1998 ST_CLERK 2600,00 124 50
144 Peter Vargas PVARGAS 650.121.2004 09.07.1998 ST_CLERK 2500,00 124 50
145 John Russell JRUSSEL 011.44.1344.429268 01.10.1996 SA_MAN 14000,00 0,40 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 05.01.1997 SA_MAN 13500,00 0,30 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10.03.1997 SA_MAN 12000,00 0,30 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15.10.1999 SA_MAN 11000,00 0,30 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29.01.2000 SA_MAN 10500,00 0,20 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30.01.1997 SA_REP 10000,00 0,30 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24.03.1997 SA_REP 9500,00 0,25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20.08.1997 SA_REP 9000,00 0,25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30.03.1998 SA_REP 8000,00 0,20 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 09.12.1998 SA_REP 7500,00 0,20 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23.11.1999 SA_REP 7000,00 0,15 145 80
156 Janette King JKING 011.44.1345.429268 30.01.1996 SA_REP 10000,00 0,35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 04.03.1996 SA_REP 9500,00 0,35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 01.08.1996 SA_REP 9000,00 0,35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10.03.1997 SA_REP 8000,00 0,30 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15.12.1997 SA_REP 7500,00 0,30 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 03.11.1998 SA_REP 7000,00 0,25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11.11.1997 SA_REP 10500,00 0,25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19.03.1999 SA_REP 9500,00 0,15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24.01.2000 SA_REP 7200,00 0,10 147 80
165 David Lee DLEE 011.44.1346.529268 23.02.2000 SA_REP 6800,00 0,10 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24.03.2000 SA_REP 6400,00 0,10 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21.04.2000 SA_REP 6200,00 0,10 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11.03.1997 SA_REP 11500,00 0,25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23.03.1998 SA_REP 10000,00 0,20 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24.01.1998 SA_REP 9600,00 0,20 148 80
171 William Smith WSMITH 011.44.1343.629268 23.02.1999 SA_REP 7400,00 0,15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24.03.1999 SA_REP 7300,00 0,15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21.04.2000 SA_REP 6100,00 0,10 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11.05.1996 SA_REP 11000,00 0,30 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19.03.1997 SA_REP 8800,00 0,25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24.03.1998 SA_REP 8600,00 0,20 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23.04.1998 SA_REP 8400,00 0,20 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24.05.1999 SA_REP 7000,00 0,15 149
179 Charles Johnson CJOHNSON 011.44.1644.429262 04.01.2000 SA_REP 6200,00 0,10 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24.01.1998 SH_CLERK 3200,00 120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23.02.1998 SH_CLERK 3100,00 120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21.06.1999 SH_CLERK 2500,00 120 50
183 Girard Geoni GGEONI 650.507.9879 03.02.2000 SH_CLERK 2800,00 120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27.01.1996 SH_CLERK 4200,00 121 50
185 Alexis Bull ABULL 650.509.2876 20.02.1997 SH_CLERK 4100,00 121 50
186 Julia Dellinger JDELLING 650.509.3876 24.06.1998 SH_CLERK 3400,00 121 50
187 Anthony Cabrio ACABRIO 650.509.4876 07.02.1999 SH_CLERK 3000,00 121 50
188 Kelly Chung KCHUNG 650.505.1876 14.06.1997 SH_CLERK 3800,00 122 50
189 Jennifer Dilly JDILLY 650.505.2876 13.08.1997 SH_CLERK 3600,00 122 50
190 Timothy Gates TGATES 650.505.3876 11.07.1998 SH_CLERK 2900,00 122 50
191 Randall Perkins RPERKINS 650.505.4876 19.12.1999 SH_CLERK 2500,00 122 50
192 Sarah Bell SBELL 650.501.1876 04.02.1996 SH_CLERK 4000,00 123 50
193 Britney Everett BEVERETT 650.501.2876 03.03.1997 SH_CLERK 3900,00 123 50
194 Samuel McCain SMCCAIN 650.501.3876 01.07.1998 SH_CLERK 3200,00 123 50
195 Vance Jones VJONES 650.501.4876 17.03.1999 SH_CLERK 2800,00 123 50
196 Alana Walsh AWALSH 650.507.9811 24.04.1998 SH_CLERK 3100,00 124 50
197 Kevin Feeney KFEENEY 650.507.9822 23.05.1998 SH_CLERK 3000,00 124 50
198 Donald OConnell DOCONNEL 650.507.9833 21.06.1999 SH_CLERK 2600,00 124 50
199 Douglas Grant DGRANT 650.507.9844 13.01.2000 SH_CLERK 2600,00 124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17.09.1987 AD_ASST 4400,00 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17.02.1996 MK_MAN 13000,00 100 20
202 Pat Fay PFAY 603.123.6666 17.08.1997 MK_REP 6000,00 201 20
203 Susan Mavris SMAVRIS 515.123.7777 07.06.1994 HR_REP 6500,00 101 40
204 Hermann Baer HBAER 515.123.8888 07.06.1994 PR_REP 10000,00 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 07.06.1994 AC_MGR 12000,00 101 110
206 William Gietz WGIETZ 515.123.8181 07.06.1994 AC_ACCOUNT 8300,00 205 110
|
|
|
|
| Re: problem with clause WITH (subquery_factoring_clause) [message #33356 is a reply to message #33346] |
Tue, 05 October 2004 16:08 |
|
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
With your complete data loaded, I get the expected results, as demonstrated below. You are using Oracle 9i, on both client and server right? The with clause (sub-query factoring clause) was not available in earlier versions. Are you running this from SQL*Plus as I am? What version of SQL*Plus are you using? If you are not running it from SQL*Plus, there are some issues that arise that do not occure in SQL*Plus. Are you running it from the hr schema or from another shcema? As I stated before, some of your table names in your queries have been prefaced with the hr schema name and some have not, so they may be querying different tables, which would explain the discrepancy.
Can you post a copy and paste of a complete run from SQL*Plus, that includes somethiing that shows your version and your schema, similar to what I have done below?
scott@ORA92> SELECT banner FROM v$version
2 /
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
scott@ORA92> SELECT user FROM global_name
2 /
USER
------------------------------
SCOTT
scott@ORA92> SELECT department_name, SUM(salary) dept_total
2 FROM employees e, departments d
3 WHERE e.department_id = d.department_id
4 GROUP BY department_name
5 /
DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Accounting 20300
Administration 4400
Executive 58000
Finance 51600
Human Resources 6500
IT 28800
Marketing 19000
Public Relations 10000
Purchasing 24900
Sales 304500
Shipping 156400
11 rows selected.
scott@ORA92> SELECT SUM (dept_total) / COUNT (*) avg
2 FROM (SELECT department_name, SUM(salary) dept_total
3 FROM employees e, departments d
4 WHERE e.department_id = d.department_id
5 GROUP BY department_name) dept_costs
6 /
AVG
----------
62218.1818
scott@ORA92> WITH
2 dept_costs AS
3 (SELECT department_name, SUM(salary) dept_total
4 FROM employees e, departments d
5 WHERE e.department_id = d.department_id
6 GROUP BY department_name),
7 avg_cost AS
8 (SELECT SUM (dept_total) / COUNT(*) avg
9 FROM dept_costs)
10 SELECT *
11 FROM dept_costs
12 WHERE dept_total >
13 (SELECT avg
14 FROM avg_cost)
15 ORDER BY department_name
16 /
DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Sales 304500
Shipping 156400
scott@ORA92>
|
|
|
|
Goto Forum:
Current Time: Sat May 18 05:28:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
