I wrote a query in Microsoft SQl Server Management Studio and is returning all the data without issue. However, this same query in the sync service connector throws an error about it being an invalid query.
The query is following the example below.
WITH TEMP1 AS ( SELECT T1.COL1, T1.COL2 FROM TABLE1 AS T1 WHERE STATUS_DATE > DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),-30) ) , TEMP2 AS ( SELECT T2.COL5, T2.COL6, T2.COL7 FROM TABLE2 AS T2 ) , TEMP3 AS ( SELECT T3.COL1, T3.COL3, T3.COL5 FROM TABL32 AS T3 ) SELECT * FROM TEMP1 AS A INNER JOIN TEMP2 AS B ON A.COL1 = B.COL5 INNER JOIN TEMP3 AS C ON A.COL3 = C.COL3
The full query has 8 individual queries in the WITH statement with more complex filtering. Each individual query does work in the sync service. But once I try to do the different joins on those temp databases, that's where it errors.
Any thoughts on this?