Sorry, it didn't work. Like I mentioned in my email, I wasn't sure if
the change was right. I was only focused on returing one row.... :-)
Good luck with that monster query...
Satheesh
Mamta Satoor wrote:
Hi Satheesh,
Thanks for all the time you spent on this.
I copied the suggested changes into my codeline and tried
running derbyall against it. The existing metadata.java and
odbc_metadata.java fail and don't return any row for getImportedKeys
test. So, looks like more tweaking is needed to fix the sql in
metadata.properties for getImportedKeys. If you/anyone else think of
any tips, please let me know. In the mean time, I will continue to work
on my end too.
thanks,
Mamta
On 1/27/06, Satheesh Bandaram <[EMAIL PROTECTED]>
wrote:
If
I add one following line, getImportedKeys returns only one row for T1.
@@ -544,6 +580,7 @@
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END)
<> 0 \
AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \
AND C.TABLEID = COLS.REFERENCEID \
+ AND CONGLOMS.CONGLOMERATENAME = C.CONSTRAINTNAME \
ORDER BY PKTABLE_CAT, \
PKTABLE_SCHEM, \
PKTABLE_NAME, \
With the change it returns two rows for T2 and no rows for
T3. I am not sure if this output is correct nor if the change is OK.
Satheesh
PS: After changing metadata.properties, a new database needs to be
created to see changed behavior.
[bandaram:satheesh] java keys T1
******* Call getImportedKeys
****************************************
Imported keys# 1
****************************************
PKTABLE_CAT:
PKTABLE_SCHEM: APP
PKTABLE_NAME: T2
PKCOLUMN_NAME: C21_ID
FKTABLE_CAT:
FKTABLE_SCHEM: APP
FKTABLE_NAME: T1
FKCOULMN_NAME: C11_ID
KEY_SEQ: 1
UPDATE_RULE: 3
DELETE_RULE: 0
FK_NAME: F_12
PK_NAME: SQL060127103319020
DEFERRABILITY: 7
****************************************
[bandaram:satheesh] java keys T2
******* Call getImportedKeys
****************************************
Imported keys# 1
****************************************
PKTABLE_CAT:
PKTABLE_SCHEM: APP
PKTABLE_NAME: T3
PKCOLUMN_NAME: C31_ID
FKTABLE_CAT:
FKTABLE_SCHEM: APP
FKTABLE_NAME: T2
FKCOULMN_NAME: C21_ID
KEY_SEQ: 1
UPDATE_RULE: 3
DELETE_RULE: 0
FK_NAME: F_443
PK_NAME: SQL060127103320650
DEFERRABILITY: 7
****************************************
****************************************
Imported keys# 2
****************************************
PKTABLE_CAT:
PKTABLE_SCHEM: APP
PKTABLE_NAME: T3
PKCOLUMN_NAME: C31_ID
FKTABLE_CAT:
FKTABLE_SCHEM: APP
FKTABLE_NAME: T2
FKCOULMN_NAME: C21_ID
KEY_SEQ: 1
UPDATE_RULE: 3
DELETE_RULE: 0
FK_NAME: F_443
PK_NAME: SQL060127103320650
DEFERRABILITY: 7
****************************************
[bandaram:satheesh] java keys T3
******* Call getImportedKeys
[bandaram:satheesh]
Satheesh Bandaram wrote:
Daniel John Debrunner wrote:
Mamta Satoor wrote:
My only advice is to break the query down from its inner elements out.
Ensure each of those in isolation is returning the correct data. Then
work on the next level out. Maybe even creating a view for the working
inner elements so the next one to tackle is somewhat readable.
E.g. with something like
SELECT * FROM T, (SELECT * FROM A,B WHERE ...) AS X
WHERE ...
I tried to break up the query and run... The inner SELECT is returning
just one row, which seems to be correct. So, I suspect we have a problem
with the outer query, which joins several system tables with the derived
table... I suspect we are missing one join condition, either between
system catalogs or between one of the system catalog and the derived table.
I will try little bit more...
Satheesh
Start with
SELECT * FROM A,B WHERE ...
ensure that works, then
do
create view SUB_AB AS SELECT * FROM A,B WHERE ...
then work on
SELECT * FROM T, SUB_AB
WHERE ...
Hope this is clear, just an idea to make the SQL visually
understandable. Maybe remove all the optimizer overrides as well to
clear out the clutter.
Dan.
|
