Basic Syntax:
<SQL> SQL expression </SQL>
The SQL statement contains standard SQL keywords and operators and Get External Data macros. The SQL statement is evaluated and then executed against the current external data source.
Get External Data macros are strings that are substituted at run time with values taken from the POD file.
The following table illustrates a simple example:
LINE-REF |
PROJECT |
AREA |
PID FROM |
PID TO |
PAINT CODE |
SG-T1 |
RM100 |
DISTILLATION |
300-020 |
300-033 |
P12 |
SG-T2 |
RM100 |
DISTILLATION |
300-021 |
300-034 |
P15 |
SG-T3 |
RM100 |
COOLING WATER |
300-022 |
300-035 |
P20 |
SG-T3 |
RM100 |
DISTILLATION |
300-023 |
300-036 |
P25 |
The SQL element is as follows:
<SQL>
SELECT * FROM PIPELINES WHERE [LINE-REF] = '$P.PIPELINE-REFERENCE$'
</SQL>
The SELECT * part of the statement returns all columns from the table PIPELINES that meet the condition in the WHERE part of the statement. In this case, the macro P.PIPELINE-REFERENCE is replaced with the PIPELINE-REFERENCE attribute of the current Pipeline. So, if PIPELINE-REFERENCE = 'SG-T1', then the first row is returned. If PIPELINE-REFERENCE = 'SG-T5', no match is found and the SQL element does not return any rows.
In some cases, a SQL statement can return more than one row. This is acceptable, provided a FILTER statement is used to further refine the selection. In other words, at a PIPELINE element, or in a COMPONENT element with no FILTER statement, the SQL statement must return one row only.