Oftentimes you need to make a more advanced join that the default LookUp Transform will allow. The challenge is when you go to the advanced option in the left hand menu, the SQL Statement is grayed out as shown below.



This is because the SQL cannot be modified when the cache setting is set to Full Cache. If you wish to modify the SQL, you’ll need to modify the General Portion of the dialog to Partial or no cache. There will be some performance tradeoff, but will give you some serious flexibility.

Possible Reasons for this type of change would include:

1. Joining between two dates to get an effective date range match.
2. Looking up based on a parameter.
3. Pass in a Join based on a loop parameter.

Whatever the reason, once you remove the Full Cache setting, you can also make cache changes here on the same tab. There are some good recommendations for this setting based on your particular application. Check out the other articles on BIDN.com for more details on this subject. Once you’re Cache setting is correct, your page should look like this.