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?
