%Join Meta-SQL Function

Your rating: None Average: 3 (1 vote)

I’m all about finding ways to type less and less code. If there is a way to shrink 4 lines of code down to 1 line, I'm all for it.

Here is a %join meta-sql that will do just that. Syntax and description from PeopleBooks are as follow:

/*Syntax*/
%Join({COMMON_KEYS | COMMON_FIELDS}, join_recname  
[ correlation_id1], to_recname [ correlation_id2]  
[, override_field_list])

/*Use the %Join meta-SQL construct to dynamically build a Where clause joining one table to another. At runtime, the entire construct is replaced with a character string. */

Lets take a look at some examples:
Three tables: TABLE1, TABLE2, and TABLE3

/*joining on common keys*/
%Join(COMMON_KEYS, TABLE1 TBL1, TABLE2 TBL2)
/*will resolve at run time to:*/
TBL1.KEY1 = TBL2.KEY1
and TBL1.KEY2 = TBL2.KEY2

/*Lest join all three tables*/
where %Join(COMMON_KEYS, TABLE1 TBL1, TABLE2 TBL2)
and %Join(COMMON_KEYS, TABLE1 TBL1, TABLE3 TBL3)
/*will resolve at run time to:*/
where TBL1.KEY1 = TBL2.KEY1
and TBL1.KEY2 = TBL2.KEY2
and TBL1.KEY1 = TBL3.KEY1
and TBL1.KEY2 = TBL3.KEY2

You can also Specify a list of fields that you do not want used in the join. For example, fields KEY1, KEY2, and KEY3 were common to two records, but you don't want to join onKEY2, then you can list KEY2 as an override_field.
%Join(COMMON_KEYS, TABLE1 TBL1, TABLE2 TBL2, KEY2)
/*will resolve at run time to:*/
TBL1.KEY1 = TBL2.KEY1
and TBL1.KEY3 = TBL2.KEY3

You can aslo join on COMMON_FIELDS the same way you do on COMMON_KEYS.

Note: If date key fields are not marked as required in the record definition for either of the referenced tables in the %Join clause, a Null clause check is added to the date field comparison. This additional clause can have a significant impact on the execution time for the generated SQL statement. (PeopleBooks)

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!