CODEWITHSUNDEEP
sqloracle-databasetoad
Steven
Steven

Reputation: 13769

Advice from Toad for Oracle Formatter

What does the warning mean?

Why is the second example worse than the first?

SELECT   product_id, prod.name name, sample_id
  FROM   lims.sample JOIN lims.product prod USING (product_id)

vs.

SELECT   product_id, prod.name name, sample_id
  FROM   (SELECT   sample_id, product_id FROM lims.sample)
         JOIN lims.product prod
/* ADVICE: [131]  This item has not been declared, or it refers to a label */
            USING (product_id)

/* ADVICE:
ADVICE SUMMARY

Count  Recommendation
-----  --------------
    1  [131]  This item has not been declared, or it refers to a label
                  The Oracle equivalent error messages are  PLS-00320 and
                  PLS-0321.
*/

FYI: Both queries run fine and return the same results.

Upvotes: 1

Views: 1605

Answers (3)

Joe
Joe

Reputation: 818

Just a guess, but in the second query your subquery is not named -- try giving it an alias; for example:

SELECT   product_id, prod.name name, sample_id
  FROM   (SELECT   sample_id, product_id FROM lims.sample) samp
         JOIN lims.product prod
            USING (product_id)

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

My guess: It looks like TOAD isn't parsing the query the same way that Oracle would.

In the first query, perhaps TOAD checks the table definitions for lims.sample and lims.product, and finds the column "product_id" in both, so it's fine.

In the second query, TOAD cannot check the table definition for the first part of the join because it's a nested query; so perhaps it gives up and gives you this advice (which is why the advice says "... or it refers to a label" which is probably a copout).

I would ignore the advice in this instance, especially as it runs fine and returns the same results.

Upvotes: 0

tom
tom

Reputation: 1639

Putting aside the tables' amount of data, indexes, and gathered statistics; in general, unnested subqueries should outperform nested subqueries.

Upvotes: 1

Related Questions