To homepage To homepage
Solved

Custom Query not working

  • 24 April 2023
  • 7 replies
  • 156 views
Stefán Gunnar Jóhannsson
Rank
Badge

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 

 

icon

Best answer by Christian Hauggaard 26 April 2023, 09:30

View original

7 replies

Stefán Gunnar Jóhannsson
Rank
Badge

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
Rank
Userlevel 1
Badge +1

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
Rank
Badge

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
Rank
Badge

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

 

 

tld
Rank
Userlevel 1
Badge +1

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
Rank
Userlevel 6
Badge +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
Rank
Userlevel 6
Badge +5

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

Reply


Terms and ConditionsCookie settings