PS Query SQL not working in Crystal SQL Designer

[agray123]

I am trying to get around using my PS Query tool as there is a security issue causing ong run times/timeouts. I created the following query and tried to paste it into Crystal SQL Designer linked into PeopleTools.

SELECT A.LAST_NAME, A.FIRST_NAME, A.MIDDLE_NAME, A.EMPLID, A.DEPTID, A.DEPTNAME, A.JOBCODE, A.JOBTITLE, A.GRADE, A.STEP, A.COMPRATE, A.UNION_CD, A.FLSA_STATUS, A.POSITION_NBR, A.HIRE_DT, A.REVIEW_DT, A.PROBATION_DT, A.REPORTS_TO, C.NAME,A.EMPL_RCD
FROM PS_EMPLOYEES A, PS_POSN_INCUMBENT B, PS_PERSONAL_VW C
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_EMPLOYEES A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= CURRENT DATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_EMPLOYEES A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.JOBCODE NOT IN ('M0510','M0509','M0513')
AND A.REPORTS_TO = B.POSITION_NBR
AND C.EMPLID = B.EMPLID )
ORDER BY 5, 1

Can anyone help me through this....it will not work!

 

[DanENW]

Have you tried your ODBC or native connection to the PS DB from the SQL Designer with a simple query using the query expert?

Next try entering this query using the query expert, less the correlated subqueries, to see if it is able to return data.

I am thinking your DB connectivity is not quite correct yet. If so, once you get it fixed everything should pop for you.

Good luck.

 

[agray123]

The reason I went this route is that there is some (as of yet undiagnosed or unsolved) issue with my query tool, or query security that has made the join wth the delivered views that make this a faster query impossible to run.
With this query I would really like to find a way to get it to work without using the tool.

I am trying to formulate another query tying some larger tables together in PS Query, however run time is a huge issue here as well.

I think the ODBC connectivity may be the root of some of the issues I am experiencing...thanks for another trail to pursue in troubleshooting.