Hi.

I'm trying to do the following:

SELECT
*
FROM
'COMPANIES.DB' COMPANIES, 'CATEGORIES.DB' CATEGORIES, 'CODESCAT.DB'
CODESCAT
WHERE
COMPANIES.IDCOMPANY = :A
AND
CODESCAT.IDCOMPANY = COMPANIES.IDCOMPANY
AND
CODESCAT.IDCATEgory = CATEGORIES.IDCATEgory (+)
ORDER BY CATEGORIES.TEXT;

When I execute the program I get the error "Invalid use of token (+)'.
I need to use (+) so I can also show the rows that don't have a match. Is
there any other way to do it?

Thanks a lot.
Julian.

P.S. The structure of the tables is the following:

COMPANIES.DB
- IDCOMPANY
- NAME

CATEGORIES.DB
-IDCATEgory
-NAME

CODESCAT.DB
-IDCOMPANY
-IDCATEgory
-TEXT

I want to show all the rows that has an specific IDCOMPANY plus the rest of
the rows that have a differen IDCATEgory from the previous rows.

On 17 Jan 1998 19:40:29 GMT, "Julian Sanchez" <jul...@w-bin.com> wrote:

Use of a "+" token is not part of the SQL-92 specification, of which local
SQL is a subset.

An equi-join, which is the type you are using, by design filters out rows
from the source table where there are no matching rows in joining tables.

You can join two tables and still retain non-matching rows from the source
table using the SQL-92 compliant LEFT OUTER join. Columns coming from rows
in joining tables where there is not a matching row in the source table are
recognized by their NULL values.

  SELECT *
  FROM 'COMPANIES.DB'
    LEFT OUTER JOIN 'CATEGORIES.DB'
      ON (COMPANIES.IDCOMPANY = CODESCAT.IDCOMPANY)
    LEFT OUTER JOIN 'CODESCAT.DB'
      ON (CATEGORIES.IDCATEgory = CODESCAT.IDCATEgory)
  WHERE (COMPANIES.IDCOMPANY = :A)
  ORDER BY CATEGORIES.TEXT;

Note that when using collation names for tables, the default is the table
name as specified in the FROM clause. Ergo, you do not need to explicitly
specify the full table name for a collation name. Functionally the same,
but shortens the statement some.

**************************************************************************
Steve Koterski
Borland International, Inc.
http://www.borland.com/delphi

mmm I get a "EDBEngineError with message 'Invalid field name. IDCOMPANY"
I have checked for typos but everything is just the way is supposed to be.
What could be wrong?

thanks a lot
Julian.

On 17 Jan 1998 22:11:52 GMT, "Julian Sanchez" <jul...@w-bin.com> wrote:

[...]

I stand corrected on this. Here are some clarifications.

If the table name is not a quoted string, the table name is the default
implicit collation name. An explicit collation name the same as the table
name need not be specified in the FROM clause and the table name can prefix
column names in other parts of the statement.

  SELECT *
  FROM CUSTOMER
    LEFT OUTER JOIN ORDERS
      ON (CUSTOMER.CUSTNO = ORDERS.CUSTNO)

If the table name is a quoted string, you need to do one of the following:

1. Prefix column names with the exact quoted string used for the table in
   the FROM clause.

   SELECT *
   FROM "CUSTOMER.DB"
     LEFT OUTER JOIN "ORDERS.DB"
       ON ("CUSTOMER.DB".CUSTNO = "ORDERS.DB".CUSTNO)

2. Use the full table name as a collation name in the FROM clause (and
   prefix all column references with the same collation name).

   SELECT *
   FROM "CUSTOMER.DB" CUSTOMER
     LEFT OUTER JOIN "ORDERS.DB" ORDERS
       ON (CUSTOMER.CUSTNO = ORDERS.CUSTNO)

3. Use a distinct token as a collation name in the FROM clause (and prefix
   all column references with the same collation name).

   SELECT *
   FROM "CUSTOMER.DB" C
     LEFT OUTER JOIN "ORDERS.DB" O
       ON (C.CUSTNO = O.CUSTNO)

**************************************************************************
Steve Koterski
Borland International, Inc.
http://www.borland.com/delphi
(Remove the "SPICEDHAM2" from the address. Death to spam-bots!)