Informatica Powercenter : SQL Hints in lookup SQL Override

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

    • mike kingslien on 22 December 2016 at 00:44
    • Reply

    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?

  1. Hi Mike, as mentioned in my post this can happen. Did you try the alternative solution mentioned?

      • mike kingslien on 27 January 2017 at 16:59
      • Reply

      You mean “use a Source Qualifier as the source of your lookup”? Unfortunately that is the only other option and yes that does work.

      1. Yes that is what i meant. Thanks for your feedback, nice to hear that you got it working.

Leave a Reply

Your email address will not be published.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close