Question

Customized Query throwing ambigious column error

  • 5 March 2024
  • 3 replies
  • 57 views
A
Userlevel 1
Badge

 Hey! I have written a query in CData Sync to load rows from TransactionAccountingLine only for a date range, Feb 2024.

REPLICATE [TransactionAccountingLine]
SELECT tranacc.* 
FROM [TransactionAccountingLine] tranacc
LEFT JOIN [transaction] tran
ON tranacc.transaction = tran.id
WHERE tran.trandate between '2024-02-01' and '2024-02-29'

Upon running this query, I am getting the error, 
[0] Column 'lastmodifieddate' is ambiguous.
 

But I should not be getting this error since  I am selecting all the columns only from the TransactionAccountingLine table, where there is only one lastmodifieddate.

Please help me with this issue.

3 replies

D
Userlevel 3
Badge +1

Sync may be appending WHERE clause on Last Modified Date, in order to do an incremental load.  But since that column is in both tables, you get an error.

To get around this -- can you create a database View for your SQL, and use that for the source of your Query?

Or disable incremental loading on this table...

A
Userlevel 1
Badge

Could you delve more into how to create a database view?
Do you mean modifying the customized query I have created?
I disabled incremental loading like you said and the query is working, but I don’t want to disable incremental loading in the long run.

D
Userlevel 3
Badge +1

Creating a view in the database may or may not be possible for your environment, and a DBA would likely need to help you with that.

 

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings