Configuration Guide

10.5 HotFix 3

Back Next

Example of a Custom Validation Rule with a Join Between Tables

You can create a condition for a custom validation rule that joins data from two tables. One table contains the parent record in the incoming data. The other table is a lookup table with a static list of values.

Use values in the parent record for conditions. If you create a condition that requires a value from a child record, the rule does not return results. You cannot join to child tables from the parent table in a custom validation rule.

You reference the incoming data as a table with the alias S. You reference the joined table with the alias I.

The following code shows a validation rule SQL statement for the join expressed by Put (BO - C_AAA_BO):

WHERE NOT EXISTS (SELECT 1 FROM C_B_LU_ADDR_TP T WHERE T.ADDR_TP = S.COL1)

The following code shows the generated SQL statement:

SELECT S.PKEY_SRC_OBJECT ,
  (SELECT 'a'  FROM dual  WHERE NOT EXISTS  (SELECT 1 FROM C_B_LU_ADDR_TP T WHERE T.ADDR_TP = S.COL1 )
  AND ROWNUM <= 1 ) RULE1
FROM
  (SELECT NULL  AS ROWID_OBJECT ,
    'SVR1.3GDX' AS PKEY_SRC_OBJECT ,
    'SYS0'      AS ROWID_SYSTEM
  FROM dual) I
CROSS JOIN
  (SELECT '1'   AS COL2 ,
    '1'         AS COL1 ,
    'SVR1.3GDX' AS PKEY_SRC_OBJECT ,
    'SYS0'      AS ROWID_SYSTEM
  FROM dual) S

Rename Saved Search

Table of Contents

Configuration Guide

Configuration Guide

Example of a Custom Validation Rule with a Join Between Tables

Example of a Custom Validation Rule with a Join Between Tables