0

This is the query I am trying to run: 

select
   ordervariety,
   bat.batch,
   order_date,
   var.variety,
   var.the_name,
   bat.the_name,
   var.the_price,
   bat.the_price
   quantity
from
   test_orders
   join TEST_ORDERVARIETIES using (orderno)
   full outer join assessment_varieties var using (variety)
   full outer join test_batches bat using (batch)
where
   ordervariety is not null

The tables are structured as follows: TEST_ORDERS:

Column Name Data Type   Nullable    Default Primary Key
ORDERNO NUMBER(6,0) No  -   1
SHOP    NUMBER(3,0) No  -   -
ORDER_DATE  DATE    No  -   -

TEST_ORDERVARIETIES:

ORDERVARIETY    NUMBER(6,0) No  -   1
ORDERNO NUMBER(6,0) No  -   -
VARIETY NUMBER(6,0) Yes -   -
BATCH   NUMBER(6,0) Yes -   -
QUANTITY    NUMBER(3,0) No  -   -

TEST_BATCHES:

Column Name Data Type   Nullable    Default Primary Key
BATCH   NUMBER(6,0) No  -   1
GREENHOUSE  NUMBER(3,0) No  -   -
THE_NAME    VARCHAR2(50)    No  -   -
VARIETY NUMBER(3,0) No  -   -
THE_PRICE   NUMBER(5,2) Yes -   -

ASSESSMENT_VARIETIES:

Column Name Data Type   Nullable    Default Primary Key
VARIETY NUMBER(6,0) No  -   1
SPECIES NUMBER(6,0) No  -   -
THE_NAME    CHAR(64)    Yes -   -
THE_PRICE   NUMBER(8,2) Yes -   -
THE_GROWING_NOTES   CHAR(255)   Yes -   -

Why do I get the error: ORA-25154: column part of USING clause cannot have qualifier?

And what can I do to fix it? Thank you!

  • 1
    http://stackoverflow.com/questions/456684/mixing-using-and-on-in-oracle-ansi-join – Andrey Belykh Dec 09 '14 at 17:42
  • Something isn't right here. You're joining to `assessment_varieties` on `variety` but according to the definitions you've posted, none of the tables you had joined up to that point have a `variety` column other than `assessment_varieties`. What do you expect that join to be doing? – Justin Cave Dec 09 '14 at 18:13
  • Sorry my fault, added the wrong table definition for assessment_varieties, check out the updated version –  Dec 09 '14 at 18:56

2 Answers2

3

Change your select to:

select
   ordervariety,
   bat.batch,
   order_date,
   variety,
---^
   var.the_name,
   bat.the_name,
   var.the_price,
   bat.the_price
   quantity

When using USING, you cannot include a table alias on the columns used in the clause.

EDIT:

The problem is that variety is in three tables, but only in the using clause for a join between two of them. I'd suggest just being explicit about the joins:

select ov.ordervariety, bat.batch, o.order_date,
       var.variety, var.the_name,
       bat.the_name, var.the_price, bat.the_price
       ov.quantity
from test_orders o join
     TEST_ORDERVARIETIES ov
     on o.orderno = ov.oderno full outer join 
     assessment_varieties var
     on var.variety = ov.variety join
     test_batches bat
     on b.batch = ov.batch
where ov.ordervariety is not null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    If I do that then I get the error: "Column ambiguously defined" because variety is in both assessment_varieties and test_batch –  Dec 09 '14 at 17:58
  • @benjs.1 . . . That would be problem. Can you include it in the `join`? Otherwise, just use explicit `on` clauses. – Gordon Linoff Dec 09 '14 at 19:25
0

Erase the bat from batch and the var from variety in the SELECT statement.

You cannot use the alias on these words in the SELECT area because you are already qualifying them in the JOIN area. 

 SELECT batch, variety, bat.the_price
Marcello B.
  • 4,177
  • 11
  • 45
  • 65
Jo-Ann
  • 1