Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle subquery - With syntax (Read 1830 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle subquery - With syntax
16.03.06 at 09:39:52
 
SQL*XL user Fred asked the following question.
 
Question:
I am using the Oracle subquery syntax. SQL*XL does not seem to recognise these with statements. Can you fix that?
 
Why not give SQL*XL a try today.
SQL*XL: SQL Excel software

Download SQL*XL now!


 
Answer:
Yes. I can confirm this is fixed in SQL*XL 4.0.42
 
The Oracle subquery syntax allows you to assign a name to a query block. You can reference this name in multiple places in the statement. The syntax is:
 
[subquery_factoring_clause] select_statement
 
[subquery_factoring_clause]:= WITH { query_name AS ( subquery ), ...}

 
Example:
A good example of the use of a with statement is when you want to calculate a percentage. Consider the emp table with columns ename, sal, deptno. An employee has a salary and a department number.  
Suppose you need to calculate what the percentage of the salary is that a person gets in a particular department.  You could use the following syntax to first calculate the total salary per department and then to join that against the emp table to compute the salary percentages:
 
with total_dept_sal as  
    (       select sum(sal) total_sal
    ,       deptno  
    from emp  
    group by deptno
    )  
select ename
,        sal
,        emp.deptno
,        round(sal*100/total_dept_sal.total_sal,0) sal_percentage  
from  emp
,        total_dept_sal  
where emp.deptno = total_dept_sal.deptno  
order by emp.deptno

 
See these pages for the full syntax:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/queries001.h tm#i2053893
Back to top
 
« Last Edit: 22.11.09 at 23:25:27 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle subquery - With syntax
Reply #1 - 03.11.07 at 21:50:27
 
I started using the with clause to make clear SQL statements where processing is done in the with statement to benefit the clarity of the statement. Consider this example:
 
select *
from   mytable m
where m.start_date >= mypackage.convert_local_to_gmt(to_date('01-01-2007','dd-mm-yyyy'))
and    m.end_date < mypackage.convert_local_to_gmt(to_date('01-01-2008','dd-mm-yyyy'))
 
Using the with syntax this can be rewritten to:
 
with parameters
as
(  select mypackage.convert_local_to_gmt(to_date('01-01-2007','dd-mm-yyyy'))
            as start_date
   ,        mypackage.convert_local_to_gmt(to_date('01-01-2008', 'dd-mm-yyyy'))
           as end_date
)
select *
from   mytable m
,         parameters p
where m.start_date >= p.start_date
and    m.end_date  <   p.end_date
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle subquery - With syntax
Reply #2 - 03.11.07 at 21:53:15
 
Another use of the with clause is to pre materialise parts of the query. The materialize hint can be used to materialise (Oracle makes a temporary table) the with parts of the query.  
 
Example:
 
with codes as (select /*+ materialize */ code from code_table)
select *
from   mytable m
,         codes c
where  m.code = c.code
 
See also:
Pre materialize parts of complex query  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1193345741/0
Back to top
 
« Last Edit: 03.11.07 at 21:53:56 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1