As you are looking for Solutions to your SYSPRO™ needs, there may be other choices, but ask them if they have been working exclusively with SYSPRO™ since 1991. Ask them if they use the same NetExpress development system and tools SYSPRO uses. Very few other companies can say yes to both answers, but we can!

Providing quality solutions to the SYSPRO™ user community since 1991!

Label SQL Interface

The labeling systems have the ability to execute a SQL SELECT statement and return the results to the labeling system. The SQL interface is done by establishing the SELECT statement in the header of the label control file and then using the SQLCMD token to execute the statement.  The SQLCOL token is used to retrieve the columns of information and the SQLNEXT token is used to fetch the next row of information.

Note: The SQL interface exists only in releases after 1/30/2013

[SQL] Label Command

The [SQL] label command must be used above the first <SQLCMD> token to establish the SELECT statement to be issued. The [SQL] command has two parts. The first part is a code (no spaces) to name the select statement. This name is used in the <SQLCMD> token to indicate which SQL statement to execute. The rest of the line is the actual SQL SELECT statement. There are some limitations and requirements for the writing of the SELECT statement.

  • All SQL words must be in upper case. These include SELECT, JOIN, AS, INNER, TOP etc.
  • All tables must us an "AS" phrase and be assigned a letter of lowercase a-z. For example "InvMaster AS a".
  • All columns must be written as "a.StockCode", etc.
  • Any column being returned by the query that is not alphanumeric needs to have (N) if it is numeric or (D) if it is a date added to the end of the column name being returned.  For example "a.JobDeliveryDate(D)"  or "b.NetUnitQtyReqd(N)".
  • The condition for any table joins must be done as part of the JOIN statement, not in the WHERE statement.  At this time only a LEFT JOIN is supported.
  • Do not use any extra spaces
  • Use the %1, %2 etc. syntax to indicate in the WHERE clause value to be inserted from the <SQLCMD> token.

<SQLCMD> token

To execute a SQL select statement, enter the <SQLCMD> token at the appropriate location in the label control file. The <SQLCMD> token has two additional parameters which are Command= and Parameters=. The full command is written as <SQLCMD(Command=XXX,Parameters=P1,P2...)>  The "Command" portion is the code used on the [SQL] statement to identify the select statement. The "Parameters" portion are any parameters to provide to the select statement.

The PARAMETERS portion is written as zero or more values separated by commas. Each of the values can be fixed information or a label token. If any value is numeric or a date it should be suffixed with a (N) or (D). For example the phrase PARAMETER={StockCode},0(N) would use the current stock code as %1 and a zero as %2.


If the [DELIMITERS] statement is used to change the default token delimiters to { and }, expand the delimiters statement with two additional characters to use for the PARAMETERS token delimiters such as < and >. Example is [DELIMITERS] {}<>.

The result of the translation of the SQLCMD token is normally blank. If there is an error in the SQL parsing or call, the result will be the SQL error message.

<SQLCOL> token

The SQLCOL token is used to specify where the columns returned from the SQL command should be used. The /? or INDEX=? parameters should be used to indicate which column in the result set to use (the default is the first column).

<SQLNEXT> token

The SQLNEXT token is used to move to the next row of the result set. This token can be used to move to the next record is a fixed number of records is expected from the SQL statement. If there are no more records, all SQLCOL tokens will be blank.

<SQLSTART> token

Sets the beginning of a loop in the control file. This token is only required if multiple records are expected from the SQL statement and a loop in the control file is being formed along with the SQLLOOP token.

<SQLLOOP> token

Gets the next SQL record and, if there is one, goes back to the point in the control file where the SQLSTART token is. If there are no more records, it continues on with the label control file.

Label control file example #1:

[SQL] CMD1 SELECT a.StockCode,a.Description,a.Decimals(N) FROM InvMaster AS a WHERE a.StockCode>=%1
[SQL] CMD2 SELECT TOP 2 a.Job,a.JobDeliveryDate(D),b.StockCode,b.NetUnitQtyReqd(N) FROM WipMaster AS a JOIN WipJobAllMat AS b ON b.Job=a.Job WHERE b.StockCode=%1 AND b.NetUnitQtyReqd>%2 ORDER BY a.JobDeliveryDate
Job Number   - Delivery Date / Stock Code / Units Required
<SQLCMD(Command=CMD2,Parameters={StockCode},0(N)><SQLCOL/1> - <SQLCOL/2> / <SQLCOL/3> / <SQLCOLl/4><SQLNEXT>
<SQLCOL/1> - <SQLCOL/2> / <SQLCOL/3> / <SQLCOL/4>

Label control file example #2 reads the LotTransaction file based on a GRN number:

[FILE] <PATH>Test-Labels\Rec-Grn-Lot-<UNIQUE>.txt
; Following line sets up the SQL SELECT statement
[SQL] GETLOT SELECT t.Lot,t.TrnQuantity(N) FROM LotTransactions AS t WHERE t.Reference='<GRN>'
; Following command executes the SQL statement to find the lot number
; The following lines would be the normal output lines - <SQLCOL/1> is the lot number