/*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. */

Example:
 
SELECT J.JPM_DATE_5
 , J.JPM_PROFILE_ID
  FROM PS_JPM_JP_ITEMS J
 WHERE J.JPM_CAT_TYPE = ‘DEG’
   AND J.EFFDT = (
 SELECT MAX (J1.EFFDT)
  FROM PS_JPM_JP_ITEMS J1
 WHERE %Join(COMMON_KEYS,JPM_JP_ITEMS J1,JPM_JP_ITEMS J,EFFDT)

Expands to:

SELECT J.JPM_DATE_5 
 , J.JPM_PROFILE_ID 
  FROM PS_JPM_JP_ITEMS J 
 WHERE J.JPM_CAT_TYPE = ‘DEG’ 
   AND J.EFFDT = ( 
 SELECT MAX (J1.EFFDT) 
  FROM PS_JPM_JP_ITEMS J1 
 WHERE J1.JPM_PROFILE_ID = J.JPM_PROFILE_ID 
   AND J1.JPM_CAT_TYPE = J.JPM_CAT_TYPE 
   AND J1.JPM_CAT_ITEM_ID = J.JPM_CAT_ITEM_ID 
   AND J1.JPM_CAT_ITEM_QUAL = J.JPM_CAT_ITEM_QUAL 
   AND J1.JPM_CAT_ITEM_QUAL2 = J.JPM_CAT_ITEM_QUAL2

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

 

Advertisements