Home » SQL & PL/SQL » SQL & PL/SQL » With Clause
With Clause [message #563972] Sun, 19 August 2012 09:07 Go to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hello,

is it in Orcale not possible to refenerence two tables in a with clause?



With CTE
AS
(
Select ...
),


CTE1

AS
(

Select ...)

Select * from CTE


If I do a select on my first table I get this error:

ORA-32035: unreferenced query name defined in WITH clause - With CTE AS

In Sql Server it works fine ...but how can I create two CTE Tables in Orcacle?

Kind regards,

Lara
Re: With Clause [message #563974 is a reply to message #563972] Sun, 19 August 2012 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
yes .... it is .... possible.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: With Clause [message #563975 is a reply to message #563972] Sun, 19 August 2012 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, the error does not mean you can't reference 2 tables but you have a (sub)query that is NOT referenced:
ORA-32035: unreferenced query name defined in WITH clause
 *Cause:  There is at least one WITH clause query name that is not
          referenced in any place.
 *Action: remove the unreferenced query name and retry

Regards
Michel

Re: With Clause [message #563976 is a reply to message #563972] Sun, 19 August 2012 09:25 Go to previous messageGo to next message
John Watson
Messages: 4863
Registered: January 2010
Location: Global Village
Senior Member
It works for me:
orcl> with cte as (select 1 from dual), cte1 as (select 2 from dual)
  2  select * from cte union all select * from cte1;

         1
----------
         1
         2

orcl> select * from v$version;

BANNER
---------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

orcl>
Re: With Clause [message #563978 is a reply to message #563976] Sun, 19 August 2012 10:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
John Watson wrote on Sun, 19 August 2012 10:25
It works for me


OP has subquery factoring clause that defines CTE and CTE1. so if main query selects from CTE and CTE1 (like you did) chain is CTE-->CTE1-->main query so both CTE and CTE1 are used. OP selected just from CTE, so chain was CTE-->main query and CTE1 was not used. Oracle, in such case raises an error, since most likely main query is incorrect, otherwise why would you write a sub-query that's not used.

SY.

[Updated on: Sun, 19 August 2012 10:28]

Report message to a moderator

Re: With Clause [message #563982 is a reply to message #563978] Sun, 19 August 2012 12:28 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hi,

Solomon..you are right..I wanted to debug something, that's way I removed one CTE...Thanks...

Anyway hopefully you can help me..it's just an example:



; with CTE

AS 

(select 1 as test
union all
 select 2 as test
union all
 select 3 as test

), 

CTE1 AS (
select 1 as test1
union all
  select 3 as test1

)

select A1.test from CTE A1, CTE1 A2 Where A1.test <> A2.test1





I want all rows of CTE which are not in CTE1....In my example the result must be 2....but I get:

2
3
1
2


How can I do it?
What's wrong with my join?
Kind regards,

Lara
Re: With Clause [message #563983 is a reply to message #563982] Sun, 19 August 2012 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

posted SQL has invalid syntax

>What's wrong with my join?
AFAIK, SELECT requires FROM clause
Re: With Clause [message #563987 is a reply to message #563982] Sun, 19 August 2012 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Solomon..you are right..I wanted to debug something, that's way I removed one CTE...Thanks...
Anyway hopefully you can help me..


It's good to thank people but when you thank one and omit the other ones then you just have less people to answer to your futur questions.
Now, I will just repeat you the forum guide you refuse to follow all along your posts.

Regards
Michel
Re: With Clause [message #563992 is a reply to message #563987] Sun, 19 August 2012 13:07 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hello Micheal,

you are right...in generall thanks for your effort...OK, I slightly struggle to post in the right way. I would post more information, but I connect to Oracle through a middleware...

Oracle Version: 11G

Operating System: Windows 2008

OK, I' rewritten the SQL..still the same problem, but now you can just copy it:


WITH CTE AS ( 

select 1 as test from dual 

union all 

select 2 as test from dual 

union all 

 select 3 as test from dual
), 

CTE1 AS ( 

select 1 as test from dual 

union all 

 select 3 as test from dual
)

Select A1.test from CTE A1, CTE1 A2 WHERE A1.test <> A2.test



The result is the same as I described before..have you an idea how du show only the entries, which are not in both tables?

Kind regards,

Lara
Re: With Clause [message #563993 is a reply to message #563982] Sun, 19 August 2012 13:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
musclebreast wrote on Sun, 19 August 2012 13:28
I want all rows of CTE which are not in CTE1.
How can I do it?
What's wrong with my join?


You do it using MINUS operator. This will give unique set of rows in CTE that are not in CTE1. Keep in mind, you need to be clear about "not in". If CTE has some row repeated, e.g. 3 times and CTE1 has same row 2 times, MINUS considers row is present in both tables. If your definition of "not in" should include that "third row" you could use analytic function ROW_NUMBER and MINUS.

SY.
Re: With Clause [message #563994 is a reply to message #563992] Sun, 19 August 2012 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
it works for me!
11:09:32 SQL> WITH cte 
     AS (SELECT 1 AS test 
         FROM   dual 
         UNION ALL 
         SELECT 2 AS test 
         FROM   dual 
         UNION ALL 
         SELECT 3 AS test 
         FROM   dual), 
     cte1 
     AS (SELECT 1 AS test 
         FROM   dual 
         UNION ALL 
         SELECT 3 AS test 
         FROM   dual) 
SELECT A1.test 
FROM   cte A1, 
       cte1 A2 
WHERE  A1.test <> A2.test 
/

      TEST
----------
	 2
	 3
	 1
	 2

11:09:53 SQL> 

[Updated on: Sun, 19 August 2012 13:14] by Moderator

Report message to a moderator

Re: With Clause [message #563995 is a reply to message #563992] Sun, 19 August 2012 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already pointed to the SQL reference manual, you have 4 main set operators.

UNION gives the list of distinct elements that are in both sets (removing duplicates):
SQL> WITH
  2   CTE AS ( 
  3    select 1 as test from dual 
  4    union all 
  5    select 2 as test from dual 
  6    union all 
  7    select 3 as test from dual
  8   ), 
  9   CTE1 AS ( 
 10    select 1 as test from dual 
 11    union all 
 12    select 3 as test from dual
 13   )
 14  select * from cte
 15  union 
 16  select * from cte1
 17  /
      TEST
----------
         1
         2
         3

3 rows selected.

UNION ALL gives all elements in both sets:
SQL> WITH
  2   CTE AS ( 
  3    select 1 as test from dual 
  4    union all 
  5    select 2 as test from dual 
  6    union all 
  7    select 3 as test from dual
  8   ), 
  9   CTE1 AS ( 
 10    select 1 as test from dual 
 11    union all 
 12    select 3 as test from dual
 13   )
 14  select * from cte
 15  union all
 16  select * from cte1
 17  /
      TEST
----------
         1
         2
         3
         1
         3

5 rows selected.

INTERSECT gives elements that are in common in sets:
SQL> WITH
  2   CTE AS ( 
  3    select 1 as test from dual 
  4    union all 
  5    select 2 as test from dual 
  6    union all 
  7    select 3 as test from dual
  8   ), 
  9   CTE1 AS ( 
 10    select 1 as test from dual 
 11    union all 
 12    select 3 as test from dual
 13   )
 14  select * from cte
 15  intersect
 16  select * from cte1
 17  /
      TEST
----------
         1
         3

2 rows selected.

MINUS gives elements that are in first set but not in second one:
SQL> WITH
  2   CTE AS ( 
  3    select 1 as test from dual 
  4    union all 
  5    select 2 as test from dual 
  6    union all 
  7    select 3 as test from dual
  8   ), 
  9   CTE1 AS ( 
 10    select 1 as test from dual 
 11    union all 
 12    select 3 as test from dual
 13   )
 14  select * from cte
 15  minus
 16  select * from cte1
 17  /
      TEST
----------
         2

1 row selected.

If you want elements that are in only one set, you can "union all" "minus" in both ways (I added one element to CTE1 to show it):
SQL> WITH
  2   CTE AS ( 
  3   select 1 as test from dual 
  4   union all 
  5   select 2 as test from dual 
  6   union all 
  7   select 3 as test from dual
  8  ), 
  9  CTE1 AS ( 
 10   select 1 as test from dual 
 11   union all 
 12   select 3 as test from dual
 13   union all
 14   select 4 as test from dual
 15   )
 16  ( select * from cte minus select * from cte1 )
 17  union all
 18  ( select * from cte1 minus select * from cte )
 19  /
      TEST
----------
         2
         4

2 rows selected.

Regards
Michel

(Edit: missing word]

[Updated on: Sun, 19 August 2012 13:22]

Report message to a moderator

Re: With Clause [message #563999 is a reply to message #563995] Sun, 19 August 2012 14:53 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hey guys,

thanks for the tons of examples...it works great now...one more question...I extended my example slightly:
WITH CTE AS (
 select 'a' as test, 't' as tr from dual union all
 select 'b' as test, 't' as tr from dual union all
 select 'c' as test, 't' as tr from dual),
 CTE1 AS ( select 'a' as test from dual union all
 select 'c' as test from dual) 
 select test from cte minus select test from cte1

As you can see..CTE has two columns now...if I choose the columns in the select it works..but what I want is that it shows all columns of the CTE in the end. In my example the result should be :

Test..TR
b.....t

How is that possible?

Kind regards and thanks in advance...

Lara

[Updated on: Sun, 19 August 2012 23:57] by Moderator

Report message to a moderator

Re: With Clause [message #564000 is a reply to message #563999] Sun, 19 August 2012 15:01 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

what is your obsession with periods ............?

[Updated on: Sun, 19 August 2012 15:05]

Report message to a moderator

Re: With Clause [message #564001 is a reply to message #564000] Sun, 19 August 2012 15:18 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member

Hi Black Swan,

what do mean with my obsession with periods? What did I wrong..I read the guideline..I compared my posting with others...What is my mistake?

I ordered the code better:


WITH CTE AS 
( 
select 'a' as test, 't' as tr from dual 
union all 
select 'b' as test, 't' as tr from dual 
union all select 'c' as test, 't' as tr from dual
), 
CTE1 AS 
( 
select 'a' as test from dual 
union all 
select 'c' as test from dual) 
 
select test from cte minus select test from cte1



As you can see..CTE has two columns now...if I choose the columns in the select it works..but what I want is that it shows all columns of the CTE in the end. In my example the result should be :

Test..TR
b.....t

How is that possible?

I hope you can help me...
Re: With Clause [message #564003 is a reply to message #564001] Sun, 19 August 2012 15:26 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
>How is that possible?
for MINUS to succeed, the columns must be the same for both SELECT in number & datatype;
which is clearly documented if you would ever Read The Fine Manual yourself!
Re: With Clause [message #564004 is a reply to message #564001] Sun, 19 August 2012 15:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
Then use NOT IN:

WITH CTE AS ( 
             select 'a' as test, 't' as tr from dual union all 
             select 'b' as test, 't' as tr from dual union all
             select 'c' as test, 't' as tr from dual
            ), 
    CTE1 AS ( 
             select 'a' as test from dual union all 
             select 'c' as test from dual
            ) 
select  *
  from  cte
  where test not in (
                     select  test
                       from  cte1
                    )
/

T T
- -
b t

SQL> 


SY.
Re: With Clause [message #564005 is a reply to message #564003] Sun, 19 August 2012 15:28 Go to previous message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Got it...I did in this way

select * from cte where test not in (select test from cte1)
Previous Topic: COLUMN TO ROW & ROW TO COLUMN
Next Topic: Bulk Update
Goto Forum:
  


Current Time: Sun Dec 21 13:27:16 CST 2014

Total time taken to generate the page: 0.14363 seconds