Home
 

 

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 that is not alphanumeric needs to have (N) is numeric or (D) if a date added to the end of the column name.  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.

The <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.

Note: 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 <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.

The <SQLNEXT> token

The SQLNEXT token is used to move to the next row of the result set.

The <SQLSTART> token

Sets the beginning of a loop in the control file.

The <SQLLOOP> token

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

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
[LABEL]
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>'
[LABEL]
; Following command executes the SQL statement to find the lot number
<SQLCMD(Command=GETLOT)><Skip>
; The following lines would be the normal output lines - <SQLCOL/1> is the lot number
<SQLSTART><SKIP>
<GRN>,<Quantity>,<SQLCOL/1>,<SQLCOL/2>
<SQLLOOP><SKIP>
[END]

Why CADACUS SOLUTIONS FOR SYSPRO?

Since 1991, as developer of the standard EDI, Shipping, and Freight Calculator Solutions for SYSPRO, our products are fully integrated with SYSPRO and drive efficiencies for hundreds of users worldwide.

Why SYSPRO?

For 35 years, SYSPRO has had a single product focus resulting in an award-winning ERP system with a track record of success for over 14,000 customers worldwide.