Home » SQL & PL/SQL » SQL & PL/SQL » ANSI join statements
ANSI join statements [message #308205] Sat, 22 March 2008 01:19 Go to next message
horax
Messages: 34
Registered: March 2008
Member
Anybody else get bugged by these? I'd much rather just do a join with teh standard <table.column>=<table.column> format.

However, that cannot always be.

That being said, can anybody explain this error to me?

SELECT b.idbasket, b.idbasket, p.productname, p.description
FROM bb_basketitem b INNER JOIN bb_product p
USING (idproduct);


I get the error SQL-25154: column part of USING clause cannot have identifier.


Ok...so it doesn't want me using idproduct in the USING clause since it's being defined by an alias in the SELECT clause?

Any suggestions here on what I hsould do?
Re: ANSI join statements [message #308207 is a reply to message #308205] Sat, 22 March 2008 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't have the definition of your tables.
Please post them.
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version (4 decimals).

You should better use SCOTT schema which is well-known in Oracle world. You can find its cration script in ORACLE_HOME/rdbms/admin.scott.sql
EMP EMPNO    NUMBER    primary key
    ENAME    VARCHAR2
    JOB      VARCHAR2
    MGR      NUMBER
    HIREDATE DATE
    SAL      NUMBER
    COMM     NUMBER
    DEPTNO   NUMBER   foreign key references DEPT

DEPT DEPTNO NUMBER    primary key
     DNAME  VARCHAR2
     LOC    VARCHAR2

In this case, in version 10.2.0.3, all the following works:
select * from emp e inner join dept d using (deptno);
select * from emp e inner join dept d on (e.deptno=d.deptno);
select * from emp e natural join dept d;


Regards
Michel
Re: ANSI join statements [message #308215 is a reply to message #308205] Sat, 22 March 2008 04:00 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Is that the actual query? That error would normally occur if you reference a column in the select clause that is used in the using clause and you use a table reference for the column in the select clause:
SQL> SELECT e.last_name, d.department_id, d.department_name
  2  FROM employees e
  3  JOIN departments d
  4  USING (department_id);
SELECT e.last_name, d.department_id, d.department_name
                    *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier


SQL> ed
Wrote file afiedt.buf

  1  SELECT e.last_name, department_id, d.department_name
  2  FROM employees e
  3  JOIN departments d
  4* USING (department_id)
SQL> /

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
Whalen                               10 Administration
Hartstein                            20 Marketing


But since you prefer Joining using the standard tab.col=tab.col why don't you just use that in the Join:
  1  SELECT e.last_name, d.department_id, d.department_name
  2  FROM employees e
  3  JOIN departments d
  4* ON e.department_id = d.department_id
Previous Topic: Disadvantages of many kinds
Next Topic: ORA-06502,urgent help req.
Goto Forum:
  


Current Time: Tue Dec 06 04:48:18 CST 2016

Total time taken to generate the page: 0.06011 seconds