ADD

Previous Next

ADD clauses in the USoft Rule Language serve to identify data queried for insert into a table. The equivalent of the SQL INSERT clause is introduced by FOR.

ADD clauses are only used in constraint SQL that (potentially) INSERTs data.

NOTE: The construct with ADD WHERE NOT EXISTS is much more compact than its SQL Equivalent, because it implies the WHERE condition and subquery that check that the record to be inserted does not already exist.

The disadvantage of ADD WHERE NOT EXISTS is that it will only let you write INSERT statements that perform this check. This is a slight disadvantage, since the vast majority of corrective INSERT constraints require this check.

Example

USoft Rule Language

SQL Equivalent

FOR            participants

(

               reservation_id

,              person_id

)

ADD WHERE NOT EXISTS

SELECT         r.reservation_id

,              p.person_id

FROM           reservation r

,              person p

WHERE          condition

INSERT INTO participant

(

             reservation_id

,            person_id

)

SELECT

             r.reservation_id

,            p.person_id

FROM         reservation r

,            person p

WHERE NOT EXISTS

(

        SELECT       ''

        FROM         participant pt

        WHERE        pt.res_id = r.res_id

        AND          pt.person_id = p.person_id

)

AND     condition