Share
Report
389I have a policy linked to a query that has CustTable as a primary table and joined with another table that stores a few customers (AccountNum).. So when the policy is applied the user can only see only few customers.
It works fine but I have a problem in Customer Aging Report... the report runs smoothly but when I go to "records to include" tab and choose Employee Responsible field the report crashes with this error:
and here's the query that fails:
"INSERT INTO tempdb."DBO".t13749IISdvhd7481vm_718228_97F2BCF42E8E47F7BCD2A58802DCF4DD (ACCOUNTNUM,GROUPID,ACCOUNTNUM,GROUPID)
SELECT T1.ACCOUNTNUM AS f1,T1.CUSTGROUP AS f2,1 AS RECVERSION,5637144576 AS PARTITION FROM CUSTTABLE T1
LEFT OUTER JOIN HCMWORKER T2 ON ((T2.PARTITION=5637144576) AND (T1.MAINCONTACTWORKER=T2.RECID))
LEFT OUTER JOIN DIRPARTYTABLE T3 ON (((T3.PARTITION=5637144576) AND (T2.PERSON=T3.RECID))
AND (T3.INSTANCERELATIONTYPE IN (3738) )) WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'atc')) AND (T3.NAME=N'John Daper'))
AND EXISTS (SELECT 'CustTablePolicy' FROM CUSTTABLE T4 CROSS JOIN XDSCUSTTABLEREGULAR T5
WHERE (((T4.PARTITION=5637144576) AND (T4.DATAAREAID=N'atc')) AND (T1.RECID=T4.RECID))
AND (((T5.PARTITION=5637144576) AND (T5.DATAAREAID=N'atc'))
AND (((T5.USERGROUPID=N'FIN') OR (T5.USERGROUPID=N'test2')) AND (T4.ACCOUNTNUM=T5.CUSTACCOUNT))))
ORDER BY T1.MAINCONTACTWORKER"when I try to remove the Employee Responsible filter from "records to include" tab and run the report it runs successfully..
and here's the succeeded query:
and here's the succeeded query:
"INSERT INTO tempdb."DBO".t13749IISdvhd7481vm_718228_E46B6BA859E84F658642F5E53359DDEA (ACCOUNTNUM,GROUPID,RECVERSION,PARTITION)
SELECT T1.ACCOUNTNUM AS f1,T1.CUSTGROUP AS f2,1 AS RECVERSION,5637144576 AS PARTITION
FROM CUSTTABLE T1 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'atc')) AND (T1.ACCOUNTNUM=N'101100000001'))
AND EXISTS (SELECT 'CustTablePolicy' FROM CUSTTABLE T2 CROSS JOIN XDSCUSTTABLEREGULAR T3
WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'atc')) AND (T1.RECID=T2.RECID))
AND (((T3.PARTITION=5637144576) AND (T3.DATAAREAID=N'atc')) AND (((T3.USERGROUPID=N'FIN')
OR (T3.USERGROUPID=N'test2')) AND (T2.ACCOUNTNUM=T3.CUSTACCOUNT))))
ORDER BY T1.ACCOUNTNUM"why in the query that fails "ACCOUNTNUM","GROUPID" is duplicated?!
Categories:
All responses (
Answers (
