Reading out a table value with SQL SELECT

Application

SQL SELECT reads a single value from a table and saves the result in the defined Q parameter.

Requirements

  • Code number 555343
  • Table exists
  • Appropriate table name
  • The names of tables and table columns must start with a letter and must not contain an arithmetic operator (e.g., +). Due to SQL commands, these characters can cause problems when data are input or read.

Description of function

Black arrows and associated syntax show internal processes of SQL SELECT

With SQL SELECT, there is neither a transaction nor a binding between the table column and Q parameter. The control does not consider any bindings that may exist to the specified column. The control copies the read value only into the parameter specified for the result.

Input

11 SQL SELECT Q5 "SELECT Mess_X FROM Tab_Example WHERE Position_NR==3"

; Save the value of the "Position_No" column of the "Tab_Example" table in Q5

To navigate to this function:

Insert NC function All functions FN SQL SQL SELECT

The NC function includes the following syntax elements:

Syntax element

Meaning

SQL SELECT

Syntax initiator for the SELECT SQL command

Q, QL, QR, QS, or Q REF

Variable in which the control stores the result

Name or QS

SQL statement or QS parameter with the definition containing:

  • SELECT: Table column of the value to be transferred
  • FROM: Synonym or absolute path of the table (path in single quotation marks)
  • WHERE: Column designation, condition, and comparison value (Q parameter after : in single quotation marks)

Fixed or variable name

Notes

  • You can select multiple values or multiple columns using the SQL command SQL EXECUTE and the SELECT statement.
  • After the WHERE syntax element, you can define the comparison value, which can also be a variable. If you use Q, QL, or QR parameters for the comparison, the control will round the defined value to the next integer. If you use a QS parameter, the control will use the exact value you specified.
  • For the instructions within the SQL command, you can likewise use single or combined QS parameters.
  • Concatenation of alphanumeric values

  • If you check the content of a QS parameter in the additional status indicator (QPARA tab), then you will see only the first 30 characters and therefore not the entire content.
  • QPARA tab

Example

The result of the following NC programs is identical.

0 BEGIN PGM SQL_READ_WMAT MM

1 SQL Q1800 "CREATE SYNONYM my_table FOR 'TNC:\table\WMAT.TAB'"

; Create synonym

2 SQL BIND QS1800 "my_table.WMAT"

; Bind QS parameters

3 SQL QL1 "SELECT WMAT FROM my_table WHERE NR==3"

; Define search

* - ...

* - ...

3 SQL SELECT QS1800 "SELECT WMAT FROM my_table WHERE NR==3"

; Read and save value

* - ...

* - ...

3 DECLARE STRING QS1 = "SELECT "

4 DECLARE STRING QS2 = "WMAT "

5 DECLARE STRING QS3 = "FROM "

6 DECLARE STRING QS4 = "my_table "

7 DECLARE STRING QS5 = "WHERE "

8 DECLARE STRING QS6 = "NR==3"

9 QS7 = QS1 || QS2 || QS3 || QS4 || QS5 || QS6

10 SQL SELECT QL1 QS7

* - ...