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. > > > > > > >
