query_peoplesoft_db
Execute SQL queries on PeopleSoft Oracle database to retrieve employee, payroll, or benefits data. Automatically follows best practices by checking table structure and code translations for accurate results.
Instructions
Query PeopleSoft Oracle Database directly.
When working with a PeopleSoft database, follow these guidelines in order:
1. FIRST, always check the record structure and fields:
- Use: SELECT RECNAME, FIELDNAME FROM PSRECFIELD WHERE RECNAME = 'YOUR_TABLE'
- This shows all fields in a record/table and helps prevent invalid field errors
2. SECOND, check field properties and translations:
- For field details: SELECT * FROM PSDBFIELD WHERE FIELDNAME = 'YOUR_FIELD'
- For code translations:
* ALWAYS check PSXLATITEM and PSXLATITEMLANG for single/double letter codes
* Example: 'M'/'F' for SEX, 'SP'/'C' for RELATIONSHIP, 'Y'/'N' for flags
* Query: SELECT FIELDNAME, FIELDVALUE, XLATLONGNAME, XLATSHORTNAME
FROM PSXLATITEM
WHERE FIELDNAME = 'YOUR_FIELD'
- Understanding field properties ensures correct data handling
3. THIRD, review table indexes for performance:
- Use: SELECT * FROM PSKEYDEFN WHERE RECNAME = 'YOUR_TABLE'
- Knowing indexes helps write efficient queries using indexed fields
4. FINALLY, write your query using the discovered structure
- Example finding employee data:
1. Check PSRECFIELD for PS_PERSONAL_DATA fields
2. Look up important fields in PSDBFIELD
3. Check PSKEYDEFN for PS_PERSONAL_DATA indexes
4. Write optimized query using indexed fields
5. Don't forget to join with PSXLATITEM for any code fields
AVAILABLE RESOURCES:
- Use describe_table() to get table structure
- Use list_tables() to search for tables
- Use get_translate_values() to decode field codes
- Use get_table_indexes() for performance optimization
:param sql_query: SQL query to execute (e.g., SELECT * FROM PS_EMPLOYEE WHERE EMPLID = :1)
:param parameters: List of query parameters (optional)
:return: A dictionary containing query results or an error message
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql_query | Yes | ||
| parameters | No |