SQL run through App Server (in PeopleCode) - results sort order different for 2 envs on same server [SEC=UNCLASSIFIED]
This is hard to explain, but I will try - We have 2 HRMS environments on the same UAT server, both identical except that one is a copy of production from 48 hours ago, the other is only 24 hours old. Both on the same Toolset (PT8.49.07) running on the same Windows 2003 servers on MS SQL 2005 (SP4).
Same build (PS_HOME etc) as far as I can tell (and both using SNAC System DSNs for ODBC database connection) - so the biggest difference is the data is 1 day different.
There is some SQL which returns 2 job data rows:
FROM PS_JOB J
WHERE J.EMPLID = '108373'
AND EMPL_RCD = 0
AND J.EFFDT = (SELECT MAX(EFFDT)
FROM PS_JOB JOB
WHERE JOB.EMPLID = J.EMPLID
AND JOB.EMPL_RCD = J.EMPL_RCD
AND JOB.EFFSEQ = J.EFFSEQ
AND JOB.EFFDT <= '2011-05-06')
AND J.EFFSEQ = (SELECT MAX(J1.EFFSEQ)
FROM PS_JOB J1
WHERE J1.EMPLID = J.EMPLID
AND J1.EMPL_RCD = J.EMPL_RCD
AND J1.EFFDT = J.EFFDT)
Note – the SQL is functionally dodgy in that it has EFFSEQ clause in EFFDT join – ignore that fact. There’s also no ORDER BY clause in there (equally dodgy).
If I run this via SQL Client against both environments I get this result set in both environments:
However, if I create a page with a button and display field, and excecute the SQL via PeopleCode against both environments (H9UATMP and H9LAST) I get this:
This only appears to have started happening in the last day, and never prior to this. No major functional changes made to job data in that period, certainly no changes for this employee. I cannot see why the SQL when run via App Server could return different results. I did a SQL trace and there was no implicit ORDER BY being added by the component processor either.
I understand the correct thing for a developer to do is specify the ORDER BY clause (and fix the EFFDT clause), so this is more of an academic question that anything else. My question is how/why would the order of the results be different in 1 environment compared to the other, when run through an app server, and not be replicable via a SQL client?
This e-mail message (along with any attachments) is intended only for the named addressee and could contain information that is confidential or privileged. If you are not the intended recipient you are notified that any dissemination, copying or use of any of the information is prohibited. Please notify us immediately by return e-mail if you are not the intended recipient and delete all copies of the original message and attachments.
This footnote also confirms that this message has been checked for computer viruses.