Skip to main content
To homepage To homepage
Solved

Custom Query not working

Stefán Gunnar Jóhannsson
TimeXtender Xpert
Forum|alt.badge.img+2

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.[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.CREATEDDATETIME as GJE_CREATEDDATETIME
      ,t2.MODIFIEDDATETIME as GJE_MODIFIEDDATETIME
      ,t2.JOURNALCATEGORY
      ,t2.FISCALCALENDARYEAR
      ,t2.FISCALCALENDARPERIOD
      ,t2.POSTINGLAYER
  FROM [AXPROD].[dbo].[GENERALJOURNALACCOUNTENTRY] t1
  LEFT OUTER JOIN [AXPROD].[dbo].GENERALJOURNALENTRY t2 on t1.GENERALJOURNALENTRY = t2.RECID
  WHERE t2.ACCOUNTINGDATE >= '2022-01-01'
    AND t2.LEDGER = 5637144577 

 

Best answer by Christian Hauggaard

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 [AXPROD].[dbo].[GENERALJOURNALACCOUNTENTRY] 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 [AXPROD].[dbo].[GENERALJOURNALENTRY]
) t2 on t1.[GENERALJOURNALENTRY] = t2.[t2RECID]
  WHERE t2.[ACCOUNTINGDATE] >= '2022-01-01'
    AND t2.[LEDGER] = 5637144577

 

View original
Did this topic help you find an answer to your question?

7 replies

Stefán Gunnar Jóhannsson
TimeXtender Xpert
Forum|alt.badge.img+2

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:

 

tld
Contributor
Forum|alt.badge.img+1
  • tld
  • Contributor
  • 13 replies
  • April 25, 2023

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

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

Thomas Duun, Atlytix - https://www.linkedin.com/in/thomasduun/
Stefán Gunnar Jóhannsson
TimeXtender Xpert
Forum|alt.badge.img+2

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

Stefán Gunnar Jóhannsson
TimeXtender Xpert
Forum|alt.badge.img+2

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

 

 

tld
Contributor
Forum|alt.badge.img+1
  • tld
  • Contributor
  • 13 replies
  • April 25, 2023

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. 

 

Thomas Duun, Atlytix - https://www.linkedin.com/in/thomasduun/
Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

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 [AXPROD].[dbo].[GENERALJOURNALACCOUNTENTRY] 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 [AXPROD].[dbo].[GENERALJOURNALENTRY]
) t2 on t1.[GENERALJOURNALENTRY] = t2.[t2RECID]
  WHERE t2.[ACCOUNTINGDATE] >= '2022-01-01'
    AND t2.[LEDGER] = 5637144577

 

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

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

Reply


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings