Rules that determine how Connect CDC grammar processes column or variable names in an expression - Connect_CDC - connect_cdc_mimix_share - Latest

Keep in mind the following rules when working with columns names or variables in an expression:

• Any identifier (a name that is not a keyword in the grammar such as an operator) is assumed to be a column name or a variable name. There should be no ambiguity in the parsing of a method name since it is always followed by a set of parentheses; for example: "my_method()". In a complex expression, a variable has to be explicitly declared before using it with a “declare” statement. Therefore, there should be no ambiguity with respect to variables. For example:

begin returns char;
    declare char my_variable_1 = "my_column";
     declare char my_variable_2 = assumed_to_be_a_column_name;
 
     if (my_variable_1 == 'foo')
          return 'bar';
    else
         return my_variable_2;
end;

• Every expression (except procedures) has a table context, the source table where the mapping containing the expression is being defined. If a name is specified and it is not a variable that was previously declared in a complex expression, it is assumed to be a column that belongs to that source table. The following examples show column references that are valid:

my_column
"my_column"
MY_TABLE.my_column
"MY_TABLE".my_column
MY_TABLE."my_column"
"MY_TABLE"."my_column"
my_schema.MY_TABLE.my_column
"my_schema"."MY_TABLE"."my_column"
"my_schema.MY_TABLE".my_column

For the following reference, parsing will fail:

"my_schema.MY_TABLE.my_column"

• Since columns have an associated table context, a column does not need to be qualified with its owning table name. However, if a join table is associated with the source table, column names, which could come from either the source table or the join table, may need to be qualified to ensure that the column reference is unambiguous. For example, if there is a column CUST_NO in the INVOICE_TABLE source table and a CUST_NO column in the CUSTOMER_TABLE join table with which it is associated, the columns should be referenced as follow:

INVOICE_TABLE.CUST_NO
"INVOICE_TABLE"."CUST_NO"
CUSTOMER_TABLE.CUST_NO
"CUSTOMER_TABLE"."CUST_NO"

• An unqualified quoted identifier is always assumed to be a column name if it is not a variable previously defined in a complex expression.

• When qualifying a column name, for example, with its table name and schema name, the qualifiers can be specified with or without double quotes. In Connect CDC grammar, a schema and table name can be specified together within double quotes, as shown in the example above:

"my_schema.MY_TABLE"