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.
- Any SQL computed values must be converted to Decimal(14,2) to stay within SYSPRO limits. For example: CONVERT(DECIMAL(14),COUNT(DISTINCT a.Item)). The (N) numeric marker must also be used.
[SQLCMD] <command>
Executes the SQL command indicated by <command>. The SQL command can not require any parameters. SQLError token will be set to any error message. Requires label system after 2021-08-27.
<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.
Notes:
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).
<SQLERROR> token
Indicates any SQL error returned from the last <SQLCMD> execution. Requires labeling system after 2021-08-27.
<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.
Example #1 - Reading information for the inventory and WIP master tables
; Following lines sets up the two possible SQL SELECT statements
[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]
; Execute the CMD1 SQL statement and use the SKIP token to not output a line to the label file
<SQLCMD(Command=CMD1,Parameters={StockCode}><SKIP>
;Output the stock code description
<SQLCOL/2>
; The next line is a header line for demonstration purposes
Job Number - Delivery Date / Stock Code / Units Required
; Execute the CMD2 SQL statement and display the first set of information
<SQLCMD(Command=CMD2,Parameters={StockCode},0(N)><SQLCOL/1> - <SQLCOL/2> / <SQLCOL/3> / <SQLCOL/4>
; Use SQLNext to go to the next record
<SQLNEXT>
; Display the next set of information
<SQLCOL/1> - <SQLCOL/2> / <SQLCOL/3> / <SQLCOL/4>
Example #2 - Reads the LotTransaction table based on a GRN number
; 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]
Example #3 - Sets a variable to the first stock code or "Mixed" based on the number of distinct stock codes on the label.
; Following line sets up the SQL SELECT statement. Note the use of CONVERT to keep the returned value within SYSPRO limits.
[SQL] CMD1 SELECT CONVERT(DECIMAL(14),COUNT(DISTINCT a.Item))(N) FROM EDILabelDetail AS a WHERE Keymark=<Mark(TRIM=LEFT)>
; Set "M" to the first stock code
[VAR] M <StockCode/1>
; Execute the SQL command
[SQLCMD] CMD1
; If the SQL command returns a value more then 1, set "M" to "Mixed"
[IFNUM] <SQLCOL/1> > 1
[VAR] M "MIXED"
[ENDIF]
Note: This example uses the [SQLCMD] command which requires label system newer then 2021-08-27. This command can be mimicked by the command [VAR] SQL <SQLCMD(Command=CMD1)>