Skip to main content
To homepage To homepage

I’ve created a query table that suddenly started to fail. When I press Validate everything is fine.

I can even preview the table in the source

I can also run the query on the source. 

But when I execute the table, with the same user as above I get this message: 

 

Here’s the query:

SELECT 
       t1. TRANSACTIONCURRENCYAMOUNT]
      ,t1./ACCOUNTINGCURRENCYAMOUNT]
      ,t1.bREPORTINGCURRENCYAMOUNT]
      ,t1.TQUANTITY]
      ,t1.TALLOCATIONLEVEL]
      ,t1.OISCORRECTION]
      ,t1.CISCREDIT]
      ,t1.,TRANSACTIONCURRENCYCODE]
      ,t1.TPAYMENTREFERENCE]
      ,t1.1POSTINGTYPE]
      ,t1.LEDGERDIMENSION]
      ,t1.GENERALJOURNALENTRY]
      ,t1. TEXT]
      ,t1.LREASONREF]
      ,t1.TPROJID_SA]
      ,t1.APROJTABLEDATAAREAID]
      ,t1.]LEDGERACCOUNT]
      ,t1.AHISTORICALEXCHANGERATEDATE]
      ,t1.      ,t1.RRECVERSION]
      ,t1.[partition]
      ,t1.>RECID]
      ,t1.OMAINACCOUNT]
      ,t1.IMODIFIEDDATETIME]
      ,t1./CREATEDDATETIME]
      ,t2.ACCOUNTINGDATE
      ,t2.DOCUMENTNUMBER
      ,t2.SUBLEDGERVOUCHER
      ,t2.SUBLEDGERVOUCHERDATAAREAID
      ,t2.JOURNALNUMBER
      ,t2.CREATEDDATETIME as GJE_CREATEDDATETIME
      ,t2.MODIFIEDDATETIME as GJE_MODIFIEDDATETIME
      ,t2.JOURNALCATEGORY
      ,t2.FISCALCALENDARYEAR
      ,t2.FISCALCALENDARPERIOD
      ,t2.POSTINGLAYER
  FROM EAXPROD]./dbo]. GENERALJOURNALACCOUNTENTRY] t1
  LEFT OUTER JOIN AAXPROD].Rdbo].GENERALJOURNALENTRY t2 on t1.GENERALJOURNALENTRY = t2.RECID
  WHERE t2.ACCOUNTINGDATE >= '2022-01-01'
    AND t2.LEDGER = 5637144577 

 

I managed to fix this but now I’m getting this error  Ambiguous column name 'MODIFIEDDATETIME' even though I have an alias on every field. 

I tried to remove t2.MODIFIEDDATETIME and then I get this error when deploying again after I’ve synchronized the data source:

 

Do you have a selection rule on the table - or incremental load enabled?

In that case, then the “Subquery needed” must be enabled.

I do have incremental load but I still get the same error after I enable subquery needed and deploy.

When I execute I get Invalid object name 'tx_etl.GENERALJOURNALACCOUNTENTRY_custom_INCR

I can see that the _INCR table doesn’t exist but I cant deploy the table to create it because I get the error from above

I don’t get the option to deploy the incremental table 

 

 

My guess is that the error comes from TimeXtender adding a where-clause with DATAAREADID=’x’ MODIFIEDDATETIME and SQL Server comlains about not knowing wether to apply MODIFIEDDATETIME to T1 or T2. So no matter if the field is part of the selection or not, then it will run into this issue.

Try if it works without Incremental load.

You could also try if it works on a standard SQL Connector instead of the AX Adapter. 

 

Hi @Stefán Gunnar Jóhannsson 

Which version of TimeXtender are you using?

A bug fix related to same error for NAV adapter was released in 20.10.27 version.

  • 13336: AX and NAV adapter sync. problem
    An issue was resolved when a field used as primary key was removed during synchronization of the adapter.
    When the error occurred a message was displayed saying "Deleted row information cannot be accessed through the row."

Furthermore, can you please try the script below?

SELECT 
       t1. TRANSACTIONCURRENCYAMOUNT]
      ,t1. ACCOUNTINGCURRENCYAMOUNT]
      ,t1. REPORTINGCURRENCYAMOUNT]
      ,t1. QUANTITY]
      ,t1. ALLOCATIONLEVEL]
      ,t1. ISCORRECTION]
      ,t1. ISCREDIT]
      ,t1. TRANSACTIONCURRENCYCODE]
      ,t1. PAYMENTREFERENCE]
      ,t1. POSTINGTYPE]
      ,t1. LEDGERDIMENSION]
      ,t1. GENERALJOURNALENTRY]
      ,t1. TEXT]
      ,t1. REASONREF]
      ,t1. PROJID_SA]
      ,t1. PROJTABLEDATAAREAID]
      ,t1. LEDGERACCOUNT]
      ,t1. HISTORICALEXCHANGERATEDATE]
      ,t1. CREATEDTRANSACTIONID]
      ,t1. RECVERSION]
      ,t1. PARTITION]
      ,t1. RECID]
      ,t1. MAINACCOUNT]
      ,t1. MODIFIEDDATETIME]
      ,t1. CREATEDDATETIME]
      ,t2. ACCOUNTINGDATE]
      ,t2. DOCUMENTNUMBER]
      ,t2. SUBLEDGERVOUCHER]
      ,t2. SUBLEDGERVOUCHERDATAAREAID]
      ,t2. JOURNALNUMBER]
      ,t2. GJE_CREATEDDATETIME]
      ,t2. GJE_MODIFIEDDATETIME]
      ,t2. JOURNALCATEGORY]
      ,t2. FISCALCALENDARYEAR]
      ,t2. FISCALCALENDARPERIOD]
      ,t2. POSTINGLAYER]

  FROM FAXPROD].Rdbo].dGENERALJOURNALACCOUNTENTRY] t1
  LEFT OUTER JOIN 
(
      SELECT
        ACCOUNTINGDATE]
      , DOCUMENTNUMBER]
      , SUBLEDGERVOUCHER]
      , SUBLEDGERVOUCHERDATAAREAID]
      , JOURNALNUMBER]
      , CREATEDDATETIME] as ]GJE_CREATEDDATETIME]
      , MODIFIEDDATETIME] as ]GJE_MODIFIEDDATETIME]
      , JOURNALCATEGORY]
      , FISCALCALENDARYEAR]
      , FISCALCALENDARPERIOD]
      , POSTINGLAYER]
      , RECID] as ]t2RECID]
      , LEDGER]
FROM FAXPROD].Rdbo].dGENERALJOURNALENTRY]
) t2 on t1.nGENERALJOURNALENTRY] = t2.=t2RECID]
  WHERE t2.EACCOUNTINGDATE] >= '2022-01-01'
    AND t2.DLEDGER] = 5637144577

 

Hi @Stefán Gunnar Jóhannsson have you had a chance to try the query posted above?

Reply


Terms and ConditionsCookie settings