--- In IBDI@egroups.com, "Dave Bishop" <dbishop@p...> wrote:

> I don't know if this is the correct forum for my question, but I am

a

> desperate man. I would appreciate any help.

I don't know if this is the correct forum either. I suspect it's
not,
but have faith that Helen will set us straight. (Smack!)

(I am a lurker here, and suspect that the MERS list would be a better
place for this question .... and if so, I'll see you there :-)

> I am working with Delphi 5, and Interbase 6. The application is
> executing on a Windows 2000 workstation. I am faced with a critical
> problem that I'm hoping someone else has experienced and/or can

help

> me resolve.
>
> I am executing a query from Delphi/Database Explorer that includes
> four tables and a single LEFT JOIN. The result set returns about 15
> fields, some numeric fields and some alphanumeric fields. No blob
> fields are included in the result set.
>
> The problem is when I execute the query, I get results that aren't
> consistent at all. The number of rows returned is consistent, but
> the data associated with some of the joined tables is sometimes
> correct and sometimes incorrect.
>

Umm, outer joins can be tricky. For example, people often get into
trouble by trying to use ON clause to filter out rows from left
table.
(Danger, danger!)

Did this query ever work "correctly"? (In other words, is this
problem
specific to IB 6?)

It would help to see what the query is, and what the current (and
expected) results look like.

Back to lurking ...

[remainder of original message follows]

> For example:
>
> Sometimes I get an employee number associated with the correct
> employee name and sometimes the employee number is associated with

a

> totally bogus name, but one that is in the database. I can run the
> query ten times in a row and get ten different result sets back

each

> time without changing a single line of the SQL command.
>
> Here are some clues/items of concern:
>
> * The DBA doesn't use any primary keys on the tables. He is of the
> belief that primary keys aren't needed. However, he does define
> unique alternate indexes on the tables. This is a concern because I
> am not sure if the BDE uses primary keys internally to resolve
> relations across table joins.
>
> * When the DBA performs an export and restores the database, the
> problem seems to go away for a period of time, although I can't
> confirm this myself. A user reported this to me.
>
> * Some users have complained that some of the drop down lists

within

> the application populate with names that are duplicated. When I
> check the query, the query is a simple select from one table. Each
> name is unique in the database. However, once again, there are no
> primary keys on the table - only unique indexes.
>
> * The problem happens on several workstations, Windows 2000 and
> Windows 98 so it doesn't seem to be workstation specific.
>
> * The problem happens in both the Delphi application and when I run
> the query from Database Explorer.
>
> I am at a total loss. My next step is to create a test database and
> apply primary keys. My hunch is that the problems are related to

the

> lack of primary keys, but I have no scientific proof of that. I

have

> been working with Delphi and Oracle for about five years and I've
> never had this problem. I am new to Interbase on a Unix server.
>
> Does anyone have any suggestions or any information regarding a
> solution? I would greatly appreciate any information anyone has.
>
> Thanks.
>
> Dave Bishop
> (847) 426-8060