Home » SQL & PL/SQL » SQL & PL/SQL » Column union (11.1.2.10)
Column union [message #673710] Thu, 06 December 2018 03:27 Go to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Hi All,

I have a lengthy query that return 3 fields (id, start_date, end_date).
-- MyQuery is:
WITH xxx as ()
SELECT id, start_date, end_date
FROM
--<......... very lengthy query


I need to have one query :
  SELECT ID, START_DATE EVENT_DATE
    FROM (MyQuery ) <-- My query
  UNION
  SELECT ID, END_DATE EVENT_DATE 
    FROM (MyQuery ) <-- My query

But I want to avoid writing the script of my query twice knowing its lengthy and for maintenance purposes. I tried WITH clause but failed. Any ideas?

Thanks,
Ferro

[Updated on: Thu, 06 December 2018 03:38]

Report message to a moderator

Re: Column union [message #673711 is a reply to message #673710] Thu, 06 December 2018 03:34 Go to previous messageGo to next message
John Watson
Messages: 7698
Registered: January 2010
Location: Global Village
Senior Member
A CTE (aka WITH CLAUSE) would seem a reasonable approach. What did you try?
Re: Column union [message #673712 is a reply to message #673711] Thu, 06 December 2018 03:42 Go to previous messageGo to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Thanks for your reply John,

MyQyery is based on a WITH Clause.
When I tried
WITH newQ as
(
  select * from 
   ( WITH xxx as
     ()
     Select id, start_date, end_date
   )
)
select id, start_date eventdate
from newq
union 
select id, end_date eventdate
from newq;


I got "1 ORA-32034: unsupported use of WITH clause "

Thanks,

[Updated on: Thu, 06 December 2018 03:43]

Report message to a moderator

Re: Column union [message #673713 is a reply to message #673712] Thu, 06 December 2018 03:47 Go to previous messageGo to next message
John Watson
Messages: 7698
Registered: January 2010
Location: Global Village
Senior Member
I can't understand that. Should it not be something like this:
orclx>
orclx> with cte as (select ename,sal,deptno from emp)
  2  select ename,sal from cte
  3  union
  4  select ename,deptno from cte
  5  /

ENAME             SAL
---------- ----------
ADAMS              20
ADAMS            1100
ALLEN              30
ALLEN            1600
BLAKE
By the way, are you sure that you want UNION? That will return only one row if end_date=start_date
Re: Column union [message #673714 is a reply to message #673713] Thu, 06 December 2018 03:54 Go to previous messageGo to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Hi John,

I thought it should work too but I guess if the inner query has a WITH clause, the query that uses union cannot have a WITH clause.
WITH newQ as    ---<--- WITH for union query
(
  select * from 
   ( WITH xxx as     --<----- WITH for MyQuery 
     ()
     Select id, start_date, end_date
   )
)
select id, start_date eventdate
from newq
union ALL     --<-- corrected
select id, end_date eventdate
from newq;

And right you are, I dont need union as I need two rows if both dates are equal. Thanks for pointing this.

Thanks,
Re: Column union [message #673715 is a reply to message #673714] Thu, 06 December 2018 04:04 Go to previous messageGo to next message
John Watson
Messages: 7698
Registered: January 2010
Location: Global Village
Senior Member
Oh, I see. Sorry for being dense. It is documented:
C:\Users\john>oerr ora 32034
32034, 00000, "unsupported use of WITH clause"
// *Cause:  Inproper use of WITH clause because one of the following two reasons:
//          1. nesting of WITH clause within WITH clause not supported yet
//          2. For a set query, WITH clause can't be specified for a branch.
//          3. WITH clause cannot be specified within parenthesis.
// *Action: correct query and retry

C:\Users\john>
One solution (not a very nice one) would be to use a global temporary table.
Re: Column union [message #673716 is a reply to message #673715] Thu, 06 December 2018 04:07 Go to previous messageGo to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Thanks John, that what I meant when I said
Quote:
I tried WITH clause but failed
I read the error and I knew it was not supported, I was seeking other ideas to avoid duplicating the lengthy script and avoid maintenance complications.

Thanks,
Ferro
Re: Column union [message #673717 is a reply to message #673716] Thu, 06 December 2018 04:13 Go to previous messageGo to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
@John using a view that presents MyQuery seems a workable workaround, what do you think?
Re: Column union [message #673722 is a reply to message #673717] Thu, 06 December 2018 11:25 Go to previous messageGo to next message
cookiemonster
Messages: 13381
Registered: September 2008
Location: Rainy Manchester
Senior Member
View would work.

But for mulitple WITH you do something like this
WITH w1 AS (SELECT <whatever>),
     w2 AS (SELECT ...
            FROM w1
            .....
           )
SELECT * FROM w2 .....

You list them sequentially and you can refer back to previous ones in the current one.

Re: Column union [message #673727 is a reply to message #673710] Thu, 06 December 2018 14:08 Go to previous message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

WITH xxx as (),
MyQuery  as (
SELECT id, start_date, end_date
FROM
--<......... very lengthy query
)
  SELECT ID, START_DATE EVENT_DATE
    FROM (MyQuery ) <-- My query
  UNION
  SELECT ID, END_DATE EVENT_DATE 
    FROM (MyQuery ) <-- My query
Previous Topic: Fill in missing data
Next Topic: RETURNING BULK COLLECT INTO COLLECTION
Goto Forum:
  


Current Time: Tue Dec 11 18:59:42 CST 2018