You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Can I join the results of two PeopleSoft queries?

1 reply [Last post]
Guest's picture
Guest

I am trying to write a query to reconcile cost balances between PeopleSoft Asset Management and the General Ledger.

I have written two queries that summarize cost by Business Unit, Account & Department. I would like to join the results of these two queries to show a side-by-side comparison of the amounts in each module - preferrably with the ability to add a column to calculate the difference. There could be a case where one BU/Account/Dept combination has a balance in one module - but not the other.

Is this possible?

Thanks,
Tina

Guest's picture
Guest (not verified)
Re: Can I join the results of two PeopleSoft queries?

Hello Tina,

I wrote a sql that matches your requirement but I have not tried it using PS query. As a sample, I took PSOPRDEFN (having OPRID and EMPLID fields) record and a custom record PS_ROLEUSER_SP (having ROLEUSER, ROLENAME and EMPLID fields). Attached is the query:
****************************************************
SELECT A.OPRID, A.EMPLID, B.ROLEUSER, B.EMPLID
FROM PSOPRDEFN A
LEFT JOIN PS_ROLEUSER_SP B
ON A.OPRID = B.ROLEUSER
UNION ALL
SELECT A.OPRID, A.EMPLID, B.ROLEUSER, B.EMPLID
FROM PS_ROLEUSER_SP B
LEFT JOIN PSOPRDEFN A
ON A.OPRID = B.ROLEUSER
WHERE A.OPRID IS NULL;
*******************************************************
With this query I am able to get the rows that have corresponding rows in the other record as well as those rows that does not match with each other (thus showing a NULL value in the fields).

I will try to get any other possible solutions if I come across.