Hi,

> I think this is a bug, The problem is quite simple, if I include an

"Alias" of a file in the "Select" I dont get a the proper value.

I don't think so :)

> Let's see the Example:
>

SELECT
Count(P1.PFIID) AS TOTALUSEDPARTS ,
Count(P2.PFIID) AS TOTALDOAPARTS
/* NOTE THAT P1 AND P2 ARE THE SAME TABLE, BUT WITH A DIFFERENT CONDITION
IN THE "AND" SECTION */
FROM
INCENG ,
AGENT ,
ENGINEER,
ACTIVINC ,
PARTSFORINCIDENT P1 ,
PARTSFORINCIDENT P2
WHERE
INCENG.IESTATUSID = 6
AND ACTIVINC.INCNO = INCENG.INCNO
AND AGENT.AGENTID = INCENG.AGENTID
AND ENGINEER.ENGINEERID = INCENG.ENGINEERID
AND P1.INCNO = INCENG.INCNO
AND P1.PARTSFAILID = 1 /* 1 = Used Part */
AND P2.INCNO = INCENG.INCNO
AND P2.PARTSFAILID = 3 /* 1 = DOA Part */
GROUP BY
AGENT.AGENTCODE , AGENT.NAME , <snip...>;

> so, the problem is that when it runs, TOTALUSEDPARTS and TOTALDOAPARTS

return the same value, but running a separated

> Select the values are different, so I guess this is a bug.
>
> Any Comments?

Yes, those are correctly returning the same values. You're INNER JOINing the
tables.

Using sub-selects is probably the thing you want (i prefer sql-92 syntax
instead of sql-89):

SELECT
INCENG.*,
AGENT.*,
ENGINEER.*,
ACTIVINC.*,
(SELECT Count(P1.PFIID) FROM PARTSFORINCIDENT P1
WHERE P1.INCNO = INCENG.INCNO
AND P1.PARTSFAILID) AS TOTALUSEDPARTS ,
(SELECT Count(P2.PFIID) FROM PARTSFORINCIDENT P2
WHERE P2.INCNO = INCENG.INCNO
AND P2.PARTSFAILID = 3) AS TOTALDOAPARTS
FROM
INCENG
JOIN AGENT ON (AGENT.AGENTID = INCENG.AGENTID)
JOIN ENGINEER ON (ENGINEER.ENGINEERID = INCENG.ENGINEERID)
JOIN ACTIVINC ON (ACTIVINC.INCNO = INCENG.INCNO)
WHERE
INCENG.IESTATUSID = 6

(of course replace the select-list with the fields you really need)


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81