Home » SQL & PL/SQL » SQL & PL/SQL » using 'with' analytical function (sql 10g)
using 'with' analytical function [message #410278] Thu, 25 June 2009 23:35 Go to next message
joec1912
Messages: 1
Registered: June 2009
Location: Thailand
Junior Member
Hi,

I am trying to avoid using temporary tables and multiple passes and PL/SQL. I want to get two subtotals in my query - total qty of order detail lines and total charges of charge lines. I am using left outer joins and was getting the charge amount incorrect (multipled by the detail lines). Any suggestions on a neat way to do this?

It has been suggested to me to use the 'with xxx as (select)' analytical function. I cant find any documetnation on this .. can anyone point me in the right direction? Does it exist?

someone sent me this pseudo code. I cant get the syntax correct in my query (I replaced the (+) owth left outer join in the from clause).

with tot_charges as (select order_number, sum(charge_amount) total_amount from order_charges group by order_number)
select OH.ORDER_NUMBER,
OD.*,
oc.*
from ORDER_HEADER OH,
ORDER_DETAIL OD,
tot_charges OC
where OH.ORDER_NUMBER = OD.ORDER_NUMBER(+)
and OH.ORDER_NUMBER = OC.ORDER_NUMBER(+)

any advice appreciated
Re: using 'with' analytical function [message #410279 is a reply to message #410278] Thu, 25 June 2009 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/index.htm#W
Re: using 'with' analytical function [message #410341 is a reply to message #410278] Fri, 26 June 2009 07:28 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
The with clause is not an analytical function.

It is a clause, or an option, of the select statement itself.

And with is the syntax word to use, but the actual name of the clause is "subquery_factoring_clause"

Look in the sql reference under the select statement for this clause.
Previous Topic: TO Loop 1 to 12
Next Topic: Data display from table
Goto Forum:
  


Current Time: Sat Dec 10 12:52:04 CST 2016

Total time taken to generate the page: 0.09827 seconds