PS Query & SQL

Good morning,

We recently had all of our packaging plans stop functioning. There were no changes to any of the equations and we were stumped. It turned out to be a SQL that all of the plan equations call. This SQL identified students in academic programs not eligible to receive aid. We found out that one of our schools added a certificate program, so we added that to the SQL in list.  It turns out that the SQL gets broken into 254-character chunks. Our new plan put us over the 254-character limit and our not inserting a space between the last character of the acad plan and the close parenthesis caused the error. Here is the text our IT was given from Oracle (I think):

Problem: When you enter SQL in the 'Equation Callable Sql Text' field on the 'Equation SQL Routines' page, the data is stored in the EQTN_SQL_TBL table in the EQTN_SQL_TEXT field. There is no limit on this field size. It is also stored in the EQTN_SQL_CHUNKS table in the EQTN_SQL_CHUNK field. This field has a length of 254, so if the SQL text is longer than 254 characters, it is split into multiple fields. When SQL ends with ')' and there is no space before it, the sentence is cut before the space.
Solution: Leave a space before   ')'  when SQL text ends with ).

I just thought I would share for those who were unaware.

Aaron

------------------------------
Aaron Tobul
Systems Analyst
University of Pittsburgh
------------------------------

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: 8/19/2026 8:25:00 AM
From: Jeffrie Brooks
Subject: RE: Fun SQL size limit Fact

Aaron,

Thanks for sharing this!

We ran into this issue a few years back with a callable SQL and somehow stumbled on the solution that we needed to take our single IN() statement and break it into 4 different IN() statements in order to get it to work. Its good to know the concrete reason why that was happening.

Jeffrie

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Original Message:
Sent: 8/19/2026 8:44:00 AM
From: Dana Pawlowicz
Subject: RE: Fun SQL size limit Fact

I had this a few years back and Vic Goldberg gave me this query.  And now, whenever I create a new callable SQL, I run it against the query.  If anything is missing, I add "AND 1 = 1" to the bottom and that fixes it.  Works every time.  You have to pay attention to your parenthesis of course.

 

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

 

 

Thanks!

Dana Pawlowicz

Business Systems Analyst Sr - ERP

Digital Platforms & Innovation
Digital Technology Solutions

University of Cincinnati

51 Goodman Dr.

Cincinnati, Oh 45221

 

 

You really just need to put anything that causes it to re-parse the SQL. I've even added a comment to make the darn thing shuffle.

------------------------------
Sarah Hoegger
Business Analyst
University of Minnesota
hoegg001@umn.edu
------------------------------

The SQL statement that will run is the concatenation of the current effective dated from table EQTN_SQL_CHUNKS. This can be different from field EQTN_SQL_TEXT on table EQTN_SQL_TBL. 

1) DELETING a row from EQTN_SQL_TBL does not automatically delete from EQTN_SQL_CHUNKS. To properly delete it you need to (a) blank out the SQL for that effective date and save, then (b) delete the effective date and save again. Simply deleting the effective date and saving will leave that effective date and SQL on EQTN_SQL_CHUNKS. 

SELECT * FROM PS_EQTN_SQL_CHUNKS A WHERE NOT EXISTS ( SELECT 1 FROM PS_EQTN_SQL_TBL B WHERE B.EQTN_OPERAND_SQL = A.EQTN_OPERAND_SQL AND B.EFFDT = A.EFFDT );

2) A few years ago, I observed that a maximum SQL length of about 7800 characters was being applied when the call SQL statement was performed by the equation engine. I don't know whether that limit is universal or whether it was specific to that installation. 

3) While rare, I have observed a few instances (at different organizations) where the concatenation from EQTN_SQL_CHUNKS did not match that on EQTN_SQL_TEXT. This is typically resolved by making a minor alteration to the offending SQL, saving, and confirming that the two tables align. 

------------------------------
Jared Jakeman
App Development and Integrations
Central Washington University
------------------------------