19.3.1.7 Building vendor-specific SQL

Common SQL does not provide a general interface to vendor-specific syntax.

There are two approaches you can take with SQL such as this:

SELECT B.PARTY_CODE_ALIAS, A.VALUE FROM CODES A, CODE_ALIASES B

     WHERE A.DOMAIN=B.CODE_DOMAIN(+) AND A.VALUE=B.CODE_VALUE(+)

           AND B.PARTY_ID(+)=<party_id>

1. Construct the string as above and then call query as described in Specifying SQL directly.
2. Use sql-expression to construct the vendor-specific pieces of the SQL. The above expression can be written like this:

(sql:select [b party_code_alias] [a value]

            :from '([codes a] [codes_aliases b])

            :where [and [= [a domain] 

                           (sql:sql-expression 

                            :string "B.CODE_DOMAIN(+)")]

                        [= (sql:sql-expression 

                            :string "B.PARTY_ID(+)") PARTY-ID]])