Column union [message #673710] |
Thu, 06 December 2018 03:27 |
|
OraFerro
Messages: 433 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 #673712 is a reply to message #673711] |
Thu, 06 December 2018 03:42 |
|
OraFerro
Messages: 433 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 |
John Watson
Messages: 8938 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 #673715 is a reply to message #673714] |
Thu, 06 December 2018 04:04 |
John Watson
Messages: 8938 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 #673722 is a reply to message #673717] |
Thu, 06 December 2018 11:25 |
cookiemonster
Messages: 13925 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 |
|
Michel Cadot
Messages: 68665 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
|
|
|