At 09:29 PM 21-08-02 -0700, you wrote:

>I have the following query:
>
>select * from products
>left outer join vendorproducts on
>prodbarcode=vpupc and prodsource=vpvendor
>where vpcost = 0
>or vpcost is null
>
>I'm using a TIBOQuery.
>
>The query executes fine. I am able to retreive the number of rows. No
>problem there.
>Then I start scrolling through the dataset. I come to a row where a
>record in products matches more than one record in vendorproducts. IBO
>gives me the following error:
>
>Multiple rows in singleton fetch.
>Check KeyLinks and JoinLinks properties.
>select * from products
>left outer join vendorproducts on
>prodbarcode=vpupc and prodsource=vpvendor
>WHERE PRODBARCODE=? /* BIND_0 */
>
>This worked perfectly fine before, under the BDE.
>
>I would be happy to check the KeyLinks and JoinLinks, except that this
>is a dynamic query, typed in by the user. The user doesn't have access
>to those properties. Even if I gave them access, they shouldn't have to
>mess with those as an end user.

That's your responsibility as the programmer - either to check the user's
entries and ensure that every row returned can be unique (the WHERE clause
contains a subclause for each Keylinks member) or, if the user's queries
are completely ad hoc, to intercept the errors and interpret them for the
user. For example, in the multiple rows in singleton select case, you
might return something like "Cannot identify the wanted record from
criteria supplied".

>It is a valid SQL statement, so it
>should work, right?

It's an ambiguous query, to begin with, but if you are not using Firebird
then you will probably be allowed to submit it. This is the correct syntax:

select * from products p
left outer join vendorproducts v on
p.prodbarcode=v.vpupc
and p.prodsource=v.vpvendor
where v.vpcost = 0
or v.vpcost is null
WHERE p.PRODBARCODE=? /* BIND_0 */

If your app expects single-row output then it's up to you as the programmer
to parse the sql entered to ensure that it can't potentially return
multiple rows.

>I do also have a "detail" TDBGrid, linking the above query to a
>TIBOTable (vendorproducts) in a master/detail relationship
>(MasterSource, etc.) on prodbarcode=vpupc. Could that be what's
>complaining?

Yes, that's what it's complaining about. If the master query returns two
rows that have the same Keylinks then there will be one detail set with two
potential master rows, violating the rules of a master-detail
relationship. Master-detail is 1:Many and can't work with Many:Many.

>TIBOTable doesn't have a JoinLinks property.

TIBOTable cannot accommodate the output from a joined query, either. Are
you saying that you are trying to use TIBOTable to do this?

TIBOQuery (the only TDataset descendant that is valid for a query) has both
Joinlinks and Keylinks and, furthermore, it cannot autodefine Keylinks for
a joined set, even an unambiguous one.

If you are using explicit join syntax (as in your example) then you don't
use Joinlinks.

>I tried
>setting KeyLinks to VPVENDOR and VPVENDPARTNO (the key fields for that
>table), but it didn't make a difference.

The Keylinks are (need to be equivalent to) a primary key. With joined
datasets (which you are presumably bringing back to a TIBOQuery somehow),
you must define a Keylinks member for each column contributing to the
unique key for the output set. As a minimum, this will be the primary keys
of all tables but, in some cases you still won't get uniqueness without
including more of the output columns.

In a "worst case", you can access the DB_KEY and use that as the Keylinks
of a joined set...but it can't work if you want that set to be the master
in a master-detail relationship, since DB_KEY is transient and has no way
to correspond to a dependency.

As a **programmer** I would regard it as a challenge to set up something
like this as a **persistent structure**, never mind attempt to build
something like it based on a totally unknown master set constructed from
user-defined, run-time building blocks. Good luck!

Helen