CHR(44) would probably work if you just needed a comma in a string output, but for something like Dana's example, the CHR(44) isn't going to evaluate before validation of the function is done and that expression has it's own parameter mismatch issue.
While this did not error out (due to the fact that TO_CHAR only has one required parameter) it does not produce the desired result
Original Message:
Sent: 04-04-2025 01:42 PM
From: Jeffrie Brooks
Subject: From PS Query to Equation SQL Routines-What's Your Process?
Has anyone ever tried concatenating in CHR(44) as a workaround for the Parms miscounts? For Dana's example it would look like this:
SELECT DISTINCT A.eqtn_operand_sql,
To_char(A.effdt || CHR(44) || 'YYYY-MM-DD'),
A.eqtn_sql_chunk_seq,
A.eqtn_sql_chunk,
A.scc_data_type_cd
FROM ps_eqtn_sql_chunks A
WHERE ( A.effdt = (SELECT Max(A_ED.effdt)
FROM ps_eqtn_sql_chunks A_ED
WHERE A.eqtn_operand_sql = A_ED.eqtn_operand_sql
AND A_ED.effdt <= SYSDATE)
AND A.eqtn_operand_sql = :1 )
ORDER BY 3
I am curious if something like that would work as well... If nobody knows, I will try to test it out next week when time allows.
Jeffrie
--
|
Jeffrie Brooks | BUSINESS SYSTEM ANALYST
UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES
|
|
Original Message:
Sent: 4/4/2025 12:33:00 PM
From: Scott Nishizaki
Subject: RE: From PS Query to Equation SQL Routines-What's Your Process?
Yes! A big plus one for Dana's point about expressions! To elaborate: When you call a SQL routine from an Equation, it uses the commas in your Select clause to determine how many parameters it expects you to pass to hold those selects. If you use an expression with commas in it, it still counts those commas.
The solution: If you need an expression for your SQL to run correctly, you can use an inline view(s). At the most basic level, this would mean wrapping your whole query in a Select * From (<your query>).
As for the other questions, we have an ODS of our most common PeopleSoft records that we can access via SQL developer, so I develop any SQL routine's there. I tend not to start from the PS Query SQL as I don't like the way PS Query lumps join criteria into the main Where clause and I would prefer far more descriptive Aliases to PS delivered A,B,C style.
------------------------------
Scott Nishizaki
Developer/Analyst
Azusa Pacific University
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
Original Message:
Sent: 04-03-2025 01:22 PM
From: Dana Pawlowicz
Subject: From PS Query to Equation SQL Routines-What's Your Process?
My answers.
- Do you use PS Query as the foundation for building your Equation SQL Routines?
I always start with PS Query. I want to know the population and I'll compare results and use individual students to test it out.
- If not, what tools or methods do you use to prototype or build the SQL before plugging it into the equation editor? (SQL Developer, Golden, plain old notepad?)
N/A
- Have you hit any snags when migrating logic from PS Query into an equation context?
I need to remember not to use expressions using commas for output or I'll get parm miscounts. And not a query thing but I always query my SQL to make sure it's chunking correctly (Thanks, Vic!).
SELECT DISTINCT A.eqtn_operand_sql,
To_char(A.effdt, 'YYYY-MM-DD'),
A.eqtn_sql_chunk_seq,
A.eqtn_sql_chunk,
A.scc_data_type_cd
FROM ps_eqtn_sql_chunks A
WHERE ( A.effdt = (SELECT Max(A_ED.effdt)
FROM ps_eqtn_sql_chunks A_ED
WHERE A.eqtn_operand_sql = A_ED.eqtn_operand_sql
AND A_ED.effdt <= SYSDATE)
AND A.eqtn_operand_sql = :1 )
ORDER BY 3
- Are there specific limitations, formatting issues, or "gotchas" that you watch out for during this transition?
No, but I tend to put each SQL into its own equation so I can test them separately or potentially reuse the code. I think this comes down to personal preference.
Original Message:
Sent: 4/3/2025 10:38:00 AM
From: Daniel Labrecque
Subject: From PS Query to Equation SQL Routines-What's Your Process?
Hey everyone,
I'm curious about how others are approaching SQL development for Equation SQL Routines, especially when those routines are called from within PeopleSoft equations.
Personally, I've found PS Query useful as a jumping-off point: it's great for prototyping joins, filters, and expressions before I translate the logic into a more formal Equation SQL Routine. But I know not everyone uses that path, so I'd love to open this up:
- Do you use PS Query as the foundation for building your Equation SQL Routines?
- If not, what tools or methods do you use to prototype or build the SQL before plugging it into the equation editor? (SQL Developer, Golden, plain old notepad?)
- Have you hit any snags when migrating logic from PS Query into an equation context?
- Are there specific limitations, formatting issues, or "gotchas" that you watch out for during this transition?
Looking forward to hearing how others tackle this-whether you're in development, business analysis, or just elbow-deep in customizations!
------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------