| Subject | Re: [IBO] HELP PLEASE: Can't put TIB_Lookup combo to work!! |
|---|---|
| Author | Helen Borrie |
| Post date | 2001-11-20T08:39:59Z |
Mirco wrote:
It's not surprising that nobody wants to try and sort this out. It is what my ancient mother calls "a dog's breakfast". I started a lengthy reply last night but, when I came to the SQL for your parent query, I said "enough!" Your SQL, which is both messy and ambiguous, is behind your current problems, as well as those you are going to bump into when you get this part of it working. Here is the SQL for the parent set:
SQL.Strings = (
'SELECT *'
'FROM ASSAY, TEST LEFT OUTER JOIN SAMPLETYPE'
'ON SAMPLETYPE.SAMPLETYPEID=TEST.SAMPLETYPEID'
'WHERE ASSAY.ASSAYID=TEST.ASSAYID')
Just a few comments, I think, to help you get this on the road to manageability, is about all I can offer right now:
1. Your SELECT * on ASSAY and TEST is, for a start, going to return redundant columns, since you have columns in both tables with the same identifiers. Begin by (a) working with an explicit field list and (b) deciding which of the common columns you want to have in your output set. You should not output ASSAYID from both tables. There might be others, too.
2. Don't use the SQL-89 syntax for your inner join. This is not IBO-friendly, anyway, and it is also part of the cause of your problem (you have the inner join criterion in the WHERE clause, but you have not defined any JoinLinks). But to add to your woes, you have mixed SQL-89 inner join syntax with outer joins (which are SQL-92). The IBO parser is smart, but it is not an artificial intelligence engine ! ;)
3. When you have worked out what your parent output set should look like and, ESPECIALLY, you have determined proper KeyLinks for that set (completely unique identification for each and every row), THEN start to look at those lookup relationships. I spent some time on them and gave up. It is what my ancient mother calls "a dog's breakfast".
4. Examine your xxxxxxSQL properties - at a glance, your editSQL won't work, because the left outer join is likely to supply nulls to the column that you are using to search for your update row. I didn't examine the other xxxxxxSQL properties further.
Pencil and paper are always handy for a task like this. Once you have corrected and simplified that parent set, then stare hard at your lookup sets and mentally trace what needs to happen to allow those self-referencing columns to follow the parent when scrolling happens and (horrors!) the parser tries to make sense of your OrderingLinks.
I strongly recommend that you try to make this structure simpler and more robust than it is. It seems to me that complex 3-way joins might not be what you want in that parent set, but to morph it into a master-detail structure...
regards,
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________
> I have reported this issue twice before, but did not receive anyMirco,
> replies with solutions to the problem. --- PLEASE HELP!!
It's not surprising that nobody wants to try and sort this out. It is what my ancient mother calls "a dog's breakfast". I started a lengthy reply last night but, when I came to the SQL for your parent query, I said "enough!" Your SQL, which is both messy and ambiguous, is behind your current problems, as well as those you are going to bump into when you get this part of it working. Here is the SQL for the parent set:
SQL.Strings = (
'SELECT *'
'FROM ASSAY, TEST LEFT OUTER JOIN SAMPLETYPE'
'ON SAMPLETYPE.SAMPLETYPEID=TEST.SAMPLETYPEID'
'WHERE ASSAY.ASSAYID=TEST.ASSAYID')
Just a few comments, I think, to help you get this on the road to manageability, is about all I can offer right now:
1. Your SELECT * on ASSAY and TEST is, for a start, going to return redundant columns, since you have columns in both tables with the same identifiers. Begin by (a) working with an explicit field list and (b) deciding which of the common columns you want to have in your output set. You should not output ASSAYID from both tables. There might be others, too.
2. Don't use the SQL-89 syntax for your inner join. This is not IBO-friendly, anyway, and it is also part of the cause of your problem (you have the inner join criterion in the WHERE clause, but you have not defined any JoinLinks). But to add to your woes, you have mixed SQL-89 inner join syntax with outer joins (which are SQL-92). The IBO parser is smart, but it is not an artificial intelligence engine ! ;)
3. When you have worked out what your parent output set should look like and, ESPECIALLY, you have determined proper KeyLinks for that set (completely unique identification for each and every row), THEN start to look at those lookup relationships. I spent some time on them and gave up. It is what my ancient mother calls "a dog's breakfast".
4. Examine your xxxxxxSQL properties - at a glance, your editSQL won't work, because the left outer join is likely to supply nulls to the column that you are using to search for your update row. I didn't examine the other xxxxxxSQL properties further.
Pencil and paper are always handy for a task like this. Once you have corrected and simplified that parent set, then stare hard at your lookup sets and mentally trace what needs to happen to allow those self-referencing columns to follow the parent when scrolling happens and (horrors!) the parser tries to make sense of your OrderingLinks.
I strongly recommend that you try to make this structure simpler and more robust than it is. It seems to me that complex 3-way joins might not be what you want in that parent set, but to morph it into a master-detail structure...
regards,
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________