2

I am trying to get an understanding of how the : variables are defined and used. I've seen them used primarily in SQL (SQLExec) commands and I have tried looking to see where they are defined.

An example is the following, to which I see :1 is being used, but I can not figure out where it is defined:

SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND 
UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &RECITEM, 
&InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM);

There are ':' variables for :1, :2, :3, :4, and :5. Any help / suggestions where specifically to learn more would be appreciated.

Nick
  • 268
  • 8
  • 33
  • fyi, these are called `bind variables` or `bind parameters`. Here some an articles with more information. 1) https://www.akadia.com/services/ora_bind_variables.html 2) https://use-the-index-luke.com/sql/where-clause/bind-parameters 3) https://www.jooq.org/doc/latest/manual/sql-building/bind-values/ – qyb2zm302 Aug 14 '18 at 23:31
  • Thank you for the information and resources! – Nick Aug 15 '18 at 12:17

1 Answers1

3

The :1,:2, etc are the next parameters in the SQLEXEC function call. So in your case

:1 = &RECITEM

:2 = &InvItemId

:3 = &VendorId

:4 = &UOM

:5 = &MaxEffdt

So now there is one extra parameter at the end, &RECITEM. This is going to store the result of the SQLexec.

A simpler to understand example might be:

SQLExec("SELECT LANGUAGE_CD, OPRDEFNDESC FROM PSOPRDEFN WHERE OPRID = :1", ACL_PRCSRUNCNTL.OPRID, &LANG_CD, &OprDescription)

:1 = ACL_PRCSRUNCNTL.OPRID

And the results of the query will be placed in:

&LANG_CD = PSOPRDEFN.LANGUAGE_CD

&OprDescription = PSOPRDEFN.OPRDEFNDESC

Darryls99
  • 921
  • 6
  • 11