By default you cannot use the following characters in an lookup sql override : ‘/*’
Because of this you cannot enter SQL Hints. You will get an error message when running your mapping. This is because the informatica parser doesn’t recognize these special characters.
To enable SQL hints in a lookup sql override you can set a custom property in the Integration Service in the Administrator Console.
- Select the Integration Service.
- Under the Properties tab, click Edit in the Custom Properties section.
- Enter a new property
- Name : LookupOverrideParsingSetting
- Value : 1.
- Click OK.
In version 9.5 there is no need to restart the IS.
Do check if other existing overrides still function as normal because i came accros an override that was going into error when we added the property. There was a TO_CHAR funtion in that query and it got messed up by informatica.
Another solution is to use a Source Qualifier as the source of your lookup. A SQ does support ‘/*’ characters by default.
4 comments
Skip to comment form
This is not a safe solution, at least it wasn’t for us. We have a maplet with a SQL override, that does NOT contain any hints or comments. After setting this custom property, Informatica rewrote an NVL2 statement incorrectly so every session containing this worklet, failed.
Has anybody else experienced anything like this?
Hi Mike, as mentioned in my post this can happen. Did you try the alternative solution mentioned?
You mean “use a Source Qualifier as the source of your lookup”? Unfortunately that is the only other option and yes that does work.
Yes that is what i meant. Thanks for your feedback, nice to hear that you got it working.