Search code examples
peoplesoftpeoplecode

PeopleSoft Query Manager - 'count' function

I'm using the current version of PeopleSoft and I'm using their Query manager. I've built a query that looks at the job table and a customized version of the job table (so I can see future hires). In order to do this I've created a union. Everything works fine, except now I want to do a count of the job codes.

enter image description here

When I put in a count, I get an error. I don't know how to get it to work properly. I also don't really know how to using the 'having' tab.

Error

I've attached some screenshots, including the SQL code.

SQL: SQL 1 SQL 2

Having tab enter image description here

Solution

  • You have a criteria in your query:

    AND COUNT(*) = A.JOBCODE

    Your job codes are string values that uniquely identify a job. It will never be equal to a count.

    If you remove that criteria, your query will work:

    working query

    Query Results

    The bigger issue is, what do you want to count? If your query was simply:

    SELECT DEPTID, JOBCODE, COUNT(*)

    This will give the count of employees in this department and job code. In your description, you said that you wanted the count of job codes. But each row has JOBCODE on it. The count of job codes on the row is one. What do you really want? The count of job codes in the database? The count of job codes in the result set?

    If you want to get anything other than the count of rows within the group, you are not able to put that logic in PeopleSoft Query. You will need to create a view in AppDesigner and then you can add that to the query.