Skip to content
SparkyaiPeopleSoft

get_sql

get_sql returns the text of a SQL definition: a SQL object, the SQL behind a SQL view, or a statement in an Application Engine program. When a definition has platform-specific versions, the tool returns the one for your database. It also lists the records that the SQL refers to.

Use it for

Section titled “Use it for”
  • “What’s the SQL behind the ZZ_TRN_PEND_VW view?”
  • “Show me the ZZ_TRAIN_PENDING SQL object.”
  • “Which records does this view read from?”

Input

Section titled “Input”
Argument Type Required Description
sql_id string Yes The SQL definition’s ID. For a view, the record name. Up to 30 characters.
type string No sql_object, view, or app_engine. Default: sql_object.
platform string No Which version to return when there are several: generic, oracle, or sqlserver. Default: the platform the server is connected to.

For an Application Engine statement, the ID comes from the sqlId of an action returned by describe_app_engine.

Input schema
{
  "type": "object",
  "properties": {
    "sql_id": {
      "type": "string",
      "minLength": 1,
      "maxLength": 30,
      "description": "SQL definition ID; for a view, the record name"
    },
    "type": {
      "enum": ["sql_object", "view", "app_engine"],
      "default": "sql_object"
    },
    "platform": { "enum": ["generic", "oracle", "sqlserver"] }
  },
  "required": ["sql_id"],
  "additionalProperties": false
}

Output

Section titled “Output”
Field Description
sqlId, type The definition and its type
platform The version returned: generic, or the platform it was written for
effectiveDate The version’s effective date
text The complete SQL text. Long statements are reassembled from the pieces they’re stored in.
recordsReferenced Records the SQL refers to, through table names such as PS_ZZ_TRAIN_REQ or meta-SQL such as %Table(ZZ_TRAIN_REQ)
lastUpdated, lastUpdatedBy When the definition last changed, and who changed it
Example: sql_id 'zz_trn_pend_vw', type view
{
  "sqlId": "ZZ_TRN_PEND_VW",
  "type": "view",
  "platform": "generic",
  "effectiveDate": "1900-01-01",
  "text": "SELECT A.EMPLID, A.COURSE, A.REQUEST_DT FROM PS_ZZ_TRAIN_REQ A WHERE A.ZZ_TRAIN_STATUS = 'P'",
  "recordsReferenced": ["ZZ_TRAIN_REQ"],
  "lastUpdated": "2026-01-20T10:05:37",
  "lastUpdatedBy": "JSMITH"
}

How it works

Section titled “How it works”

SQL definitions live in two tables. PSSQLDEFN has one row per definition, with its last-change details, and PSSQLTEXTDEFN holds the text, keyed by SQLID, SQLTYPE, MARKET, DBTYPE, EFFDT, and SEQNUM. Both SQLTYPE and DBTYPE are character columns:

Argument value Column and code
type: sql_object SQLTYPE = '0'
type: app_engine SQLTYPE = '1'
type: view SQLTYPE = '2'
platform: generic DBTYPE = ' '
platform: oracle DBTYPE = '2'
platform: sqlserver DBTYPE = '7'

The server reads the generic version and the version for the requested platform together:

Versions of a SQL definition (Oracle Database)
SELECT DBTYPE, EFFDT, SEQNUM, SQLTEXT
  FROM PSSQLTEXTDEFN
 WHERE SQLID = :sql_id
   AND SQLTYPE = :sqltype
   AND DBTYPE IN (' ', :dbtype)
   AND EFFDT <= SYSDATE
 ORDER BY DBTYPE DESC, EFFDT DESC, SEQNUM

Sorting by DBTYPE in descending order puts a platform-specific version, such as '2', ahead of the generic ' ', so the first version in the result is the one that applies. Within that version, the server keeps the latest effective date and joins the rows in SEQNUM order. Application Engine statements can also vary by market; the server resolves those the same way, falling back to GBL.

Limits and errors

Section titled “Limits and errors”
  • The text comes back as stored, including meta-SQL such as %Table, %Select, and %DateIn. The tool doesn’t expand meta-SQL, because PeopleTools expands it at runtime, in context.
  • recordsReferenced comes from pattern matching on the text, so it can miss tables that PeopleCode builds into SQL strings at runtime.
  • An ID that doesn’t exist returns a tool execution error that lists the closest IDs of that type.
Section titled “Related tools”