Search code examples
peoplesoftpeoplecode

Reusing SQL statement in PeopleCode for prompt table

I have a page in PeopleSoft that has a number of similar fields. Each one uses a prompt table that is set to a dynamic view. When one field is changed, it populates the SQLText property of two other fields, incorporating the value that was just chosen. Since this SQL statement is essentially the same each time around, I was hoping I could do something like this:

/* UM_SUBPLAN_SQL contains something to the effect of:
   SELECT DISTINCT a.acad_sub_plan 
      , a.descr 
   FROM ps_acad_subpln_tbl a 
   WHERE a.institution =:1 
      AND a.acad_plan =:2 
*/
&loSQL = GetSQL(SQL.UM_SUBPLAN_SQLTEXT, "UMS04", "ENT-BS");
&lcSQLText = &loSQL.Value;
&loSQL.Close();
RECORD.FIELD.SqlText = &lcSQLText;

Unfortunately, the SQL.Value property returns the string containing the bind variables :1 and :2, rather than the values that are being bound. Is there any way to get that string with the values substituted in, short of doing the same sort of string concatenation I'm trying to get away from here? Thanks.

Edit: Well, it wasn't exactly what I wanted, but I went with the following. It works, if a bit wordier than I was hoping for.

&loSQL = GetSQL(SQL.UM_SUBPLAN_SQLTEXT, "X", "X");
&lcSQLText = Substitute(&loSQL.Value, ":1", "'" | RECORD.FIELD1.Value | "'")
&lcSQLText = Substitute(&lcSQLText, ":2", "'" | RECORD.FIELD2.Value | "'");
&loSQL.Close();

If you have a cleaner solution, I'd love to hear it. Thanks again!

Edit again: Peter's comment below let me clean this up quite a bit. Thanks!

&loSQL = GetSQL(SQL.UM_SUBPLAN_SQLTEXT, "X", "X");
&lcSQLText = ExpandSqlBinds(&loSQL.Value, RECORD.FIELD1.Value, RECORD.FIELD2.Value);
&loSQL.Close();
Solution

  • Try ExpandSqlBinds and ExpandBindVar.