|Subject||Re: [IBO] IBO Doesn't like this query|
> >It is a valid SQL statement, so itI am using Firebird, and it DOES allow me to submit 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:That last line was not part of my original query. IBO is generating that part
> select * from products p
> left outer join vendorproducts v on
> and p.prodsource=v.vpvendor
> where v.vpcost = 0
> or v.vpcost is null
> WHERE p.PRODBARCODE=? /* BIND_0 */
(for the master-detail maybe?). What is that ? /* BIND_0 */ about anyway?
> If your app expects single-row output then it's up to you as the programmerI don't care about multiple rows. It's fine with me if it displays it multiple
> to parse the sql entered to ensure that it can't potentially return
> multiple rows.
times in my dataset. That's what the BDE did.
> >I do also have a "detail" TDBGrid, linking the above query to aWhy not? It works fine with the BDE. When I run this query with the BDE, it
> >TIBOTable (vendorproducts) in a master/detail relationship
> >(MasterSource, etc.) on prodbarcode=vpupc. Could that be what's
> 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.
returns multiple rows in the master dataset, and they simply match to the exact
same rows in the detail dataset. That worked fine with me then. Why won't IBO
do the same?
> >TIBOTable doesn't have a JoinLinks property.No. I was saying the the DETAIL dataset is a TIBOTable. The MASTER dataset
> TIBOTable cannot accommodate the output from a joined query, either. Are
> you saying that you are trying to use TIBOTable to do this?
(the output from the joined query) is a TIBOQuery.
> TIBOQuery (the only TDataset descendant that is valid for a query) has bothRight. The query is outputting non-unique rows, so there's really no valid
> 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.
Keylinks value that I could use that would make the dataset unique.
> As a **programmer** I would regard it as a challenge to set up somethingOk. True. It would be near to impossible to make this master-detail
> 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!
relationship work ALL THE TIME, when the user can enter any old ad hoc query
that they want. I accept that. My issue is that this used to work OK under the
BDE (or it made a reasonable interpretation, instead of throwing an exception).
The fact is that I wrote this query for a customer of mine to get a certain
result. I'm actually OK if I can find a different query that achieves the same
goal. Maybe a join isn't the best way to do it. Here's what I'm trying to do,
in plain english:
I want to find all of the records in the PRODUCTS table that either:
A) Don't match any records in the VENDORPRODUCTS table
B) If they DO match a record in VENDORPRODUCTS, the VPCOST field in
VENDORPRODUCTS is either 0 or NULL.
In these cases, the "match" criteria is that PRODUCTS.PRODBARCODE =
VENDORPRODUCTS.VPUPC and PRODUCTS.PRODSOURCE = VENDORPRODUCTS.VPVENDOR.
Basically, I'm trying to find all PRODUCTS that don't link to a nonzero cost.
Is there a better way to do this?