1

I am building a query in people soft using Peoplesoft query manager.

I am trying to pull the most recent date from the date column. I have tried using max() as an expression, however, the query doesn't pull any records. I have checked with another co-worker and they have never been able to pull records using max().

Is there any other way or workaround to pull the most recent record?

Stevoisiak
  • 23,794
  • 27
  • 122
  • 225
Ryan
  • 19
  • 2
  • 3
  • there is always `select top 1 date from table order by date desc`...which rdbms? – dotjoe Aug 13 '12 at 18:45
  • PeopleSoft HRMS 8.90 that uses Oracle – Ryan Aug 13 '12 at 18:49
  • Run this and post: SELECT * FROM v$version – Roberto Navarro Aug 13 '12 at 23:54
  • I can't run SQL, PeopleSoft generates that according to the expressions, criteria, having, prompts and records that you create via PeopleSoft. I would if I had access to SQL developer to alter the SQL code but unfortunately I do not. The max function seems to be working when one record is present but when I join multiple records it is returning nothing in the Query. Not sure what the problem is – Ryan Aug 14 '12 at 13:10

4 Answers4

0

So I figured out why no results were returned when using Max in a subquery. It was more from a lack of understanding PeopleSoft and SQL since I am relatively new to it. When I was setting the date column in the subquery as max for the aggregate to be used as criteria to compare to the date column in the main query I didn't make any criteria in the subquery. This meant that the subquery would go through all dates for all employees except for the employee that I was specifying in a prompt and returning a value that didn't match any of the dates for the employee in the main query and returning no one. This was fixed by setting a criteria in the subquery that the employee ID that had to be searched in the subquery matched the one that was typed into the prompt in the main query

Ryan
  • 19
  • 2
  • 3
0

Use effective date for doing such searches while using PSQuery.

user1658435
  • 574
  • 1
  • 9
  • 28
0

Use Effective date in order to get the most recent date, max may not work properly in PeopleSoft. Query should be effective dated

user1658435
  • 574
  • 1
  • 9
  • 28
0

PS Query has built in filters for EFFDT tables. When you add a criteria on the EFFDT field, there are some additional drop down choices on the "condition type" field like 'Eff Date <' and 'Eff Date <=', etc. Usually, when you create a query for an Effective dated table, PS Query will automatically add the subquery based on the 'Eff Date <=' condition type.

Donald Shin
  • 26
  • 3