Refines the results of evaluation the SQL expression and is always used in conjunction with an SQL element. The result of the FILTER statement must be to return one row from the result of the SQL query.
Basic syntax:
<FILTER> expression </FILTER>
Consider the following example:
LINE-REF |
ITEM_CODE |
Bore1 |
Bore2 |
Schedule |
Material |
Type |
SG-T1 |
GCA150-RG2 |
6 |
6 |
STD |
NYLON |
GASKET |
SG-T1 |
FCD150-WNRSTD |
6 |
6 |
STD |
CS ASTM A105 |
FLANGE |
SG-T1 |
PA5BSTD |
6 |
6 |
STD |
CS API 5L |
PIPE |
SG-T1 |
EEL90CFSTD-LBW |
6 |
6 |
STD |
FRG CS A234 WPB |
ELBOW |
SG-T1 |
VVGCF150-FLO |
6 |
6 |
STD |
CS ASTM A234 |
VALVE |
SG-T1 |
T1RCFSTD-BW |
6 |
4 |
STD |
FRG CS A234 |
TEE |
SG-T1 |
PA5BSTD |
4 |
4 |
STD |
CS API 5L |
PIPE |
SG-T1 |
EEL90CFSTD-LBW |
4 |
4 |
STD |
FRG CS A234 WPB |
ELBOW |
SG-T1 |
FCD150-WNRSTD |
4 |
4 |
STD |
CS ASTM A105 |
FLANGE |
SG-T1 |
GCA150-RG2 |
4 |
4 |
STD |
NYLON |
GASKET |
SG-T1 |
VVGCF150-FLO |
4 |
4 |
STD |
CS ASTM A234 |
VALVE |
The following SQL statement returns all the rows in the table when PIPELINE-REFERENCE = 'SG-T1' and no rows otherwise.
<SQL>
SELECT * FROM COMPONENTS WHERE [LINE-REF] = '$P.PIPELINE-REFERENCE$'
</SQL>
The FILTER statement is evaluated at each component in turn.
<FILTER>
[ITEM_CODE] = '$M.ItemCode$' AND Bore1 = $M.SIZE1$
</FILTER>
It must return one row, based on the criterion in the FILTER statement. At a component with ItemCode = VVGCF150-FLO and Size1 = 4, the only row that matches is the last one in the table.
A FILTER statement can contain simple comparisons (=, <, >, <>) and logical operators, (AND, OR).
-
If there is a SQL element and a FILTER element, the SQL element is evaluated only once. The FILTER element is evaluated at each component, material, information item, or joint.
-
Advantages - This is efficient when the SQL statement is slow to execute. The FILTER element operates on the result of the SQL query.
-
Disadvantages - It is not possible to use joint component properties in macros in the SQL statement.
-
-
If there is no FILTER element, the SQL element is evaluated at each joint.
-
Advantages - Joint properties can be included in macros in the SQL statement.
-
Disadvantages - Can be slow as the SQL statement is executed many times.
-