Ask Question
Help Others
Home › PeopleSoft › PeopleCode › Meta-SQL Reference

%Join Meta-SQL Function

Thu, 02/14/2008 - 10:20am — Lepa
No votes yet

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)

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
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!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.