Subject Re: Debugging Ambiguous Errors
Author Matt Nielsen
So, I keep on answering my own questions. Sorry to the group for
cluttering things up.

After digging into some of the IBO code and looking at the trace logs
I found out that the KeyLinks property could not be calculated
properly and it had a bogus value in it that was causing the
ambiguous error. That would also explain why this only happend on an
update.

Matt
--- In IBObjects@yahoogroups.com, "Matt Nielsen" <mnielsen@c...>
wrote:
>
> I thought I had this nailed with Firebird, but I'm getting some
> ambiguous field name errors still with Firebird 1.5.1. These errors
> don't happen in IB 6, but I understand that they are potential
> problems in the queries. However I only get the error when I
attempt
> to do an update to the data. I've looked through the query it
seems
> to be complaining about and I can't find any problems with it at
> all. What can I do to find out what the problem really is?
>
> Here is one of the queries it is complaining about:
>
> SELECT
> POHEADERITEMS.SEQNO,
> POHEADERITEMS.PURCHORDERID,
> POHEADERITEMS.IMPORTID,
> POHEADERITEMS.SELECTED,
> POHEADERITEMS.JOBID,
> POHEADERITEMS.WORKORDERID,
> POHEADERITEMS.WORKORDERITEMID,
> POHEADERITEMS.ENTITYID,
> POHEADERITEMS.SPLITID,
> POHEADERITEMS.TASKMATERIALID,
> POHEADERITEMS.MATERIALID,
> POHEADERITEMS.CHOICEID,
> POHEADERITEMS.ACCOUNTID,
> POHEADERITEMS.ACCOUNTID2,
> POHEADERITEMS.ISEXPENSE,
> POHEADERITEMS.PRICE,
> POHEADERITEMS.ORDERUNITS,
> POHEADERITEMS.DELIVERUNITS,
> POHEADERITEMS.RECEIVEDUNITS,
> POHEADERITEMS.ADDDESCRIPT,
> POHEADERITEMS.LOCATION,
> POHEADERITEMS.REFERENCE,
> POHEADERITEMS.OVERRIDE,
> POHEADERITEMS.CREATEBYNAME,
> POHEADERITEMS.CREATEDON,
> POHEADERITEMS.MODIFIEDBYNAME,
> POHEADERITEMS.MODIFIEDON,
> POHEADER.TABLEID AS POHEADER_TABLEID,
> POIMPORT.TABLEID AS POIMPORT_TABLEID,
> WORKORDER.TABLEID AS WORKORDER_TABLEID,
> ENTITY.DESCRIPT AS ENTITY_NAME,
> MATERIAL.TABLEID AS MATERIAL_TABLEID,
> MATERIAL.IDCODE AS MATERIAL_IDCODE,
> MATERIAL.DESCRIPT AS MATERIAL_NAME,
> MATERIALUNITMEASURE.DESCRIPT AS UOM_NAME,
> ACCOUNT1.IDCODE AS GLACCOUNT_IDCODE,
> ACCOUNT1.NAME AS GLACCOUNT_NAME,
> ACCOUNT2.IDCODE AS GLACCOUNT2_IDCODE,
> ACCOUNT2.NAME AS GLACCOUNT2_NAME,
> PLANPHASE.PHASEINDEX AS PLANPHASE_INDEX,
> PLANPHASE.DESCRIPT AS PLANPHASE_NAME,
> PLANOPTION.IDCODE AS PLANOPTION_IDCODE,
> PLANOPTION.DESCRIPT AS PLANOPTION_NAME,
> PROJECT.TABLEID AS PROJECT_TABLEID,
> JOB.TABLEID AS JOB_TABLEID,
> POHEADERITEMS.PRICE * POHEADERITEMS.ORDERUNITS AS EXT_PRICE
> FROM POHEADERITEMS
> LEFT OUTER JOIN POHEADER
> ON (POHEADERITEMS.PURCHORDERID = POHEADER.SEQNO)
> LEFT OUTER JOIN POIMPORT
> ON (POHEADERITEMS.IMPORTID = POIMPORT.SEQNO)
> LEFT OUTER JOIN WORKORDER
> ON (POHEADERITEMS.WORKORDERID = WORKORDER.SEQNO)
> LEFT OUTER JOIN ENTITY
> ON (POHEADERITEMS.ENTITYID = ENTITY.SEQNO)
> LEFT OUTER JOIN MATERIAL
> ON (POHEADERITEMS.MATERIALID = MATERIAL.SEQNO)
> LEFT OUTER JOIN MATERIALUNITMEASURE
> ON (MATERIAL.TAKEOFFUM = MATERIALUNITMEASURE.SEQNO)
> LEFT OUTER JOIN GLACCOUNTS ACCOUNT1
> ON (POHEADERITEMS.ACCOUNTID = ACCOUNT1.SEQNO)
> LEFT OUTER JOIN GLACCOUNTS ACCOUNT2
> ON (POHEADERITEMS.ACCOUNTID2 = ACCOUNT2.SEQNO)
> LEFT OUTER JOIN WORKORDERTAKEOFF
> ON (POHEADERITEMS.WORKORDERITEMID = WORKORDERTAKEOFF.SEQNO)
> LEFT OUTER JOIN PLANOPTION
> ON (WORKORDERTAKEOFF.OPTIONID = PLANOPTION.SEQNO)
> LEFT OUTER JOIN PLANPHASE
> ON (WORKORDERTAKEOFF.PHASEID = PLANPHASE.SEQNO)
> LEFT OUTER JOIN PROJECT
> ON (WORKORDER.PROJECTID = PROJECT.SEQNO)
> LEFT OUTER JOIN JOB
> ON (POHEADERITEMS.JOBID = JOB.SEQNO)
>
> The error is :
>
> Ambiguous fieldname between table POHEADERITEMS and POHEADER and
> table POIMPORT and table WORKORDER and table ENTITY and SEQNO.
>
> Again this is on the update only. I'm using a IBOQuery with
update,
> insert, delete SQL statements in the component.
>
> Here is the update statement:
>
> UPDATE POHEADERITEMS
> SET
> SEQNO = :SEQNO,
> PURCHORDERID = :PURCHORDERID,
> IMPORTID = :IMPORTID,
> SELECTED = :SELECTED,
> JOBID = :JOBID,
> WORKORDERID = :WORKORDERID,
> WORKORDERITEMID = :WORKORDERITEMID,
> ENTITYID = :ENTITYID,
> SPLITID = :SPLITID,
> TASKMATERIALID = :TASKMATERIALID,
> MATERIALID = :MATERIALID,
> CHOICEID = :CHOICEID,
> ACCOUNTID = :ACCOUNTID,
> ACCOUNTID2 = :ACCOUNTID2,
> ISEXPENSE = :ISEXPENSE,
> PRICE = :PRICE,
> ORDERUNITS = :ORDERUNITS,
> DELIVERUNITS = :DELIVERUNITS,
> RECEIVEDUNITS = :RECEIVEDUNITS,
> ADDDESCRIPT = :ADDDESCRIPT,
> LOCATION = :LOCATION,
> REFERENCE = :REFERENCE,
> OVERRIDE = :OVERRIDE,
> CREATEBYNAME = :CREATEBYNAME,
> CREATEDON = :CREATEDON,
> MODIFIEDBYNAME = :MODIFIEDBYNAME,
> MODIFIEDON = :MODIFIEDON
> WHERE
> SEQNO = :OLD_SEQNO