|Subject||Re: [firebird-support] False result in query|
At 12:33 AM 3/02/2007, you wrote:
>Hi,Why do you double all the relation names?????
>I have a weird problem.
>In a system we need to print batches of labels and therefor we issue a
>query to a firebird database which contains a few joins.
>It has to do with requested investigations (ANALYSIS) by a customer
>which contains several materials (SPECIMEN) and several
>testing-procedures (STAINING) to be performed on little pieces
>(CASSETTE) taken from the materials.
>The query issued is :
>SELECT Analysis.Analysis, Analysis.AN_NUMBER, Analysis.An_Origin,
> Specimen.Specimen, Specimen.Number as SpecIndex, Specimen.Macroscopy,
> Cassette.Cassette, Cassette.Number as CassIndex,
> Sample.Sample, Sample.Number, Sample.Request,
> Staining.NAME as Staining, EXC.Exc_Type FROM SAMPLE SAMPLE
> LEFT OUTER JOIN EXC EXC ON EXC.SAMPLE = SAMPLE.SAMPLE
> INNER JOIN STAINING Staining ON SAMPLE.STAINING = STAINING.STAINING
> INNER JOIN CASSETTE Cassette ON CASSETTE.CASSETTE = SAMPLE.CASSETTE
> INNER JOIN SPECIMEN Specimen ON SPECIMEN.SPECIMEN = CASSETTE.SPECIMEN
> INNER JOIN ANALYSIS ANALYSIS ON ANALYSIS.ANALYSIS = SPECIMEN.ANALYSIS
> WHERE Sample.PRINTBATCH=%d ORDER BY Analysis.AN_NUMBER,
>Specimen.Number,Cassette.NUMBER, Staining.NAME, Exc.Exc
>This system has been functioning flawless in several enterprises for
>over 5 years, until 2 months ago in 1 enterprise we appear to have
>received a false result twice, being the investigation-number printed
>not matching the real item in the database. All other information is
>correct, it just seems that the join to the analysis-table, having a
>unique key created by a generator, returns the wrong row.
>This happens for just one sample,while other samples connected to the
>same cassette display the proper analysisnumber (AN_NUMBER).
>We examined the database, and everything seems to be ok. Also we found
>no signs of corruption using the standard firebird tools ( gfix ).
>Also no interesting entries in Firebird.log.
>If we request a reprint of the wrong label for that specific
>investigation we get the proper information.
>The firebird-engine we use is 22.214.171.12470 .
>Development in Delphi, using DbExpress.
>Firebird engine runs on W2K server on VmWare as service superserver mode.
>The difference with the past is that the engine has been upgraded from
>1.5.2 and the server is migrated to VmWare.
>Has anybody experienced a problem like this before ?.
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>Also search the knowledgebases at http://www.ibphoenix.com
>Yahoo! Groups Links