Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Combining WITH clause, ORA-32034
nomadetech
post May 30 2007, 04:30 AM
Post #1


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



Hello,
I have a query that requires WITH clauses since it reiterates a similar select, so I'm down with 2 WITH clause which I cannot unionize. How can I do this?
Thanks Pierre

with DURCDR as (select CALLINGPARTYNUMBER,CALLINGPARTYIMSI,CALLINGPARTYIMEI,CALLEDPARTYNUMBER,CALLEDPAR
TYIMSI,CALLEDPARTYIMEI,
CHARGINGSTARTTIME,REDIRECTINGPARTYIMEI,REDIRECTINGPARTYIMSI,REDIRECTINGPARTYNUMB
ER from bgw_durcdr where CHARGINGSTARTTIME BETWEEN '01-05-2007:00:00:00' and '01-05-2007:23:59:59'
and (CALLEDPARTYIMEI IN '2980' or CALLINGPARTYIMEI IN '2980' OR REDIRECTINGPARTYIMEI IN '2980'))
SELECT
CALLEDPARTYNUMBER,
CALLEDPARTYIMSI,
CALLEDPARTYIMEI,
CHARGINGSTARTTIME
FROM DURCDR
UNION
SELECT
CALLINGPARTYNUMBER,
CALLINGPARTYIMSI,
CALLINGPARTYIMEI,
CHARGINGSTARTTIME
FROM DURCDR
UNION
SELECT
REDIRECTINGPARTYIMEI,
REDIRECTINGPARTYIMSI,
REDIRECTINGPARTYNUMBER,
CHARGINGSTARTTIME
FROM
DURCDR GET ERROR ORA-32034
UNION
WITH EVTCDR AS(select DESTINATION,DESTINATIONIMSI,DESTINATIONIMEI,CHARGINGSTARTTIME,ORIGINIMEI,ORIGINI
MSI,ORIGIN,REDIRECTINGPA
RTYIMEI,REDIRECTINGPARTYIMSI,REDIRECTINGPARTYNUMBER FROM BGW_EVTCDR WHERE CHARGINGSTARTTIME BETWEEN '01-05-2007:00:00:00' and '01-05-2007:23:59:59'
AND( DESTINATIONIMEI IN '2980' OR ORIGINIMEI IN '2980' OR REDIRECTINGPARTYIMEI IN '2980'))
SELECT
DESTINATION,
DESTINATIONIMSI,
DESTINATIONIMEI,
CHARGINGSTARTTIME
FROM EVTCDR
UNION
SELECT
ORIGINIMEI,
ORIGINIMSI,
ORIGIN,
CHARGINGSTARTTIME
FROM EVTCDR
UNION
SELECT
REDIRECTINGPARTYIMEI,
REDIRECTINGPARTYIMSI,
REDIRECTINGPARTYNUMBER,
CHARGINGSTARTTIME
FROM EVTCDR;
Go to the top of the page
 
+Quote Post
burleson
post May 30 2007, 04:37 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,634
Joined: 26-January 04
Member No.: 13



Hi,

The WITH clause is intended to materialize intermediate row sets, just like global temporary tables.

See these notes:

http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm

For your query, I think that you want something like this, doing the union in the WITH secvtion, before the select:

WITH
select stuff . . . .
UNION
select more stuff
select
. . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
nomadetech
post May 30 2007, 06:27 AM
Post #3


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



Thanks,
with S as (Select...),
D as (select...),
E as (select...)
select ...
union...

Works great.
Pierre

QUOTE (burleson @ May 30 2007, 11:38 AM) *
Hi,

The WITH clause is intended to materialize intermediate row sets, just like global temporary tables.

See these notes:

http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm

For your query, I think that you want something like this, doing the union in the WITH secvtion, before the select:

WITH
select stuff . . . .
UNION
select more stuff
select
. . . .
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 31st October 2014 - 02:14 PM