Pulling the top 2 rows as well as New Hires on a table...

[HRISCONSULTANT]

A user is getting expected results with the following...

(SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = JOB.EMPLID
AND A.EFFDT < JOB.EFFDT)

Job is the first ps_job
Job1 is the second ps_job

There is a problem however when there is a new hire or conversion (no records returned). I've suggested using a CASE STATEMENT for when the count was greater than on or just one but the user is getting the following...

I tried your CASE statement within the select and also as a stand-alone SQL criteria. In both tests, I get a "group function not allowed here" error message. I also tried appending the SQL to the existing condition and got "missing right parentheses".

The statement I provided him is...
CASE JOB1.EFFDT WHEN COUNT(*)>1 THEN (SELECT MAX (A.EFFDT) FROM PS_JOB A
WHERE A.EMPLID=JOB.EMPLID AND A.EFFDT<JOB.EFFDT) ELSE (SELECT A.EFFDT FROM
PS_JOB WHERE A.EMPLID=JOB.EMPLID AND A.EFFDT=JOB.EFFDT) END


Mind you my programming is a bit rusty - any suggestion greatly appreaciated...

 

[CharlesCook]

Without geting into the how you are doing it, just tell me what you want returned.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[HRISCONSULTANT]

Basically, the program is to return the last two rows for a report. It is working fine for all but new hires and conversions where there is only one record - at that point nothing is returned. In attempting to use the CASE statement is not quite correct as the error messages noted in the original email are received...

 

[CharlesCook]

Got ya,

I think the problem is in your selection critera. You need to specify that a null condition is a posability.

Can you copy the SQL that is getting generated from the version WITH OUT the Case statement. And Post it here.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[HRISCONSULTANT]

This is not pulling the single row records and is not creating an error...

(SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = JOB.EMPLID AND A.EFFDT < JOB.EFFDT)

where Job is the first ps_job and Job1 is the second ps_job

As for stating that null is a possibility, I attempted to cover that with the CASE statement but apparently not... a recommendation I received was to have two PS_JOB tables where I would be looking for the current row in one and current less one in the other which sounds like it could be more complicated than it needs to be (as in, why not do this with one table?)...

 

[CharlesCook]

This is ALL of the sql in your report? I just want to be clear you report only returns 1 column? Why are there prens before the Select and at the end?

(SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = JOB.EMPLID AND A.EFFDT < JOB.EFFDT)


CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[christimess]

Hi,
What you will want is for your 1st JOB table to be max effective date so that it gets the latest.
That line should read:
data field JOB.EFFDT is equal to formula (SELECT MAX(JOB.EFFDT) FROM PS_JOB WHERE JOB.EMPLID=JOB.EMPLID)

For the next JOB table you will want to enter a new selection list and change "all of the following apply:" to "any of the following apply". You will need two statments here.
2.1 Data field JOB1.EFFDT is equal to formula (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = JOB.EMPLID AND A.EFFDT < JOB.EFFDT)
2.2 Data field JOB1.EFFDT is null

This should fix your problem.

 

[CharlesCook]

I think you are right christimess.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[HRISCONSULTANT]

Thanks Charles, thanks Christine

 

[ReportSmithing]

There's a big problem with the above statements - EFFSEQ is not factored into your code. For instance, there COULD be two effective dates that are equal, yet the sequence numbers can be changed (0 and 1) in which case the 0 sequence row should reflect your prior row.

To deal with the problem (below is in Oracle), you can concatenate EFFDT and EFFSEQ and in turn convert the value into a number and then work in your operator.

(SELECT MAX(TO_NUMBER(TO_CHAR(X.EFFDT, 'YYYYMMDD')) || TO_NUMBER(X.EFFSEQ))
FROM PS_JOB X
WHERE (X.EMPLID = JOB1.EMPLID) AND
(TO_NUMBER(TO_CHAR(X.EFFDT, 'YYYYMMDD')) || TO_NUMBER(X.EFFSEQ) < TO_NUMBER(TO_CHAR(ADJ1.EFFDT, 'YYYYMMDD')) || TO_NUMBER(ADJ1.EFFSEQ)))

 

[CharlesCook]

Yep ReportSmithing you cant forget EFFSEQ. You really need to see all of the SQL to make it work right.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining