PS Query & SQL

Vic Goldberg taught me this trick.  If you know your Callable SQL is correct but you are testing and it's giving you errors.  Check to make sure all of your SQL is being saved.    Run this query and if not all of your SQL is there, try adding "AND 1 = 1" to the end of it and check it again.

I've always check it now just to be on the safe side.

    
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

------------------------------
Dana Pawlowicz
Business Systems Analyst Sr - ERP
University of Cincinnati
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Yeah, I think there's a bug in the parsing code for the Equation page, and depending on the characters it's parsing when it reaches the chunk size limit, it doesn't trigger creation of the next chunk. I find this tends to happen when parsing a list of values, more so than when it's parsing keywords. Adding "AND 1=1" along the way is a great way to fool the parser.

You'd think this would be an easy bug for Oracle to fix. ;)

------------------------------
Mark Russom B.Sc. FLMI ACS
Business Systems Lead
McMaster University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------