Home » SQL & PL/SQL » SQL & PL/SQL » Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why?
icon5.gif  Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251229] Thu, 12 July 2007 13:19 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
I have looked within the forum for the answer to this question but have not discovered one yet.

In Oracle 10.2.0.1 this old ANSI join syntax works for a correlated subquery ...

select a.x
       , ( select c.z
           from   table_b   b
                , table_c   c
           where a.x = b.x
           and   b.y = c.y
         ) as z_val
from   table_a   a


This new ANSI join syntax does not work ...

select a.x
       , ( select c.z
           from   table_b   b
           join   table_c   c
           on     b.y = c.y
           and    a.x = b.x
         ) as z_val
from   table_a   a


The 2nd SQL statment produces the error:

ORA-00904: "a"."x": invalid identifier


Why?

Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251234 is a reply to message #251229] Thu, 12 July 2007 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Does this work any better?
select a.x
       , ( select c.z
           from   table_c   c
           join   table_b   b
           on     b.y = c.y
           and    a.x = b.x
         ) as z_val
from   table_a   a
Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251238 is a reply to message #251229] Thu, 12 July 2007 13:36 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
No. It produced the same error.

But ... this did work ...

select a.x
       , ( select c.z
           from   table_b   b
           join   table_c   c
           on     b.y = c.y

           where  a.x = b.x
         ) as z_val
from   table_a   a


Why?
Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251240 is a reply to message #251238] Thu, 12 July 2007 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No specific reason.
Syntax analyzer is not at the same level for both syntax.

If you post create table statements, I can try it on different versions.

Regards
Michel
Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251245 is a reply to message #251229] Thu, 12 July 2007 14:01 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thanks for the reply.

The example I gave was very simplified.

There are actually more than 3 tables involved and the DDL might be a bit overwhelming if I sent it all.

You could probably create:
table_a with PK x
table_b with PK x,y
table_c with PK y
to do the test.

I am not sure if this is totally correct, but for now, I am assuming that a column from an outer query can only be referenced in the WHERE clause of a correlated subquery.


Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251247 is a reply to message #251245] Thu, 12 July 2007 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There are actually more than 3 tables involved and the DDL might be a bit overwhelming if I sent it all.

Ah ha, so what you posted is not the real problem.
Maybe you do something wrong but we can't see it.
Try to build a small example with the same issue.
Otherwise, it does not exist.

Regards
Michel
Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251257 is a reply to message #251229] Thu, 12 July 2007 14:25 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
I created the simplified tables and produced the same results.
Below is all the code:

CREATE TABLE GACNTR1.table_a
(
    x_col       VARCHAR2(10) NOT NULL
)
TABLESPACE DATA
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
/
ALTER TABLE GACNTR1.table_a 
    ADD CONSTRAINT table_a_PK
PRIMARY KEY (x_col)
USING INDEX TABLESPACE DATA
            PCTFREE 10
            INITRANS 2
            MAXTRANS 255
            STORAGE(BUFFER_POOL DEFAULT)
    LOGGING
    ENABLE
    VALIDATE
/
----------------------------

CREATE TABLE GACNTR1.table_b
(
    x_col       VARCHAR2(10) NOT NULL
    , y_col       VARCHAR2(10) NOT NULL
)
TABLESPACE DATA
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
/
ALTER TABLE GACNTR1.table_b
    ADD CONSTRAINT table_b_PK
PRIMARY KEY (x_col,y_col)
USING INDEX TABLESPACE DATA
            PCTFREE 10
            INITRANS 2
            MAXTRANS 255
            STORAGE(BUFFER_POOL DEFAULT)
    LOGGING
    ENABLE
    VALIDATE
/

----------------------------

CREATE TABLE GACNTR1.table_c
(
    y_col       VARCHAR2(10) NOT NULL
    , z_col       VARCHAR2(10) NOT NULL
)
TABLESPACE DATA
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
/
ALTER TABLE GACNTR1.table_c
    ADD CONSTRAINT table_c_PK
PRIMARY KEY (y_col)
USING INDEX TABLESPACE DATA
            PCTFREE 10
            INITRANS 2
            MAXTRANS 255
            STORAGE(BUFFER_POOL DEFAULT)
    LOGGING
    ENABLE
    VALIDATE
/


This SQL did not work ...

select a.x_col
       , ( select c.z_col
           from   table_b   b
           join   table_c   c
           on     b.y_col = c.y_col
           and    a.x_col = b.x_col
         ) as z_val
from   table_a   a


It produced the error:

1	7	19	ORA-00904: "A"."X_COL": invalid identifier



This SQL did work ...

select a.x_col
       , ( select c.z_col
           from   table_b   b
           join   table_c   c
           on     b.y_col = c.y_col
           
           where  a.x_col = b.x_col
         ) as z_val
from   table_a   a



I am looking at syntax from:

OracleŽ Database SQL Reference
10g Release 2 (10.2)
Part Number B14200-02

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2130078


but do not see any explanation yet.


Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251261 is a reply to message #251257] Thu, 12 July 2007 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"a.x_col" is not part of the join between "b" and "c", so you can't put it in the "on" clause.

Regards
Michel
Re: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why? [message #251346 is a reply to message #251261] Fri, 13 July 2007 01:33 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel nailed it, I think. The on clause is for defining the relations. You want a where clause.

Oracle documentation agrees:
Quote:
Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause.


MHE
Previous Topic: Bulk process with limit advice
Next Topic: Error handling in bulk insert/merge
Goto Forum:
  


Current Time: Sun Dec 04 16:35:49 CST 2016

Total time taken to generate the page: 0.07352 seconds