in Technology by
When do you use SQL override in a lookup transformation?

1 Answer

0 votes
by

You should override the lookup query in the following circumstances:

  1. Override the ORDER BY clause. Create the ORDER BY clause with fewer columns to increase performance. When you override the ORDER BY clause, you must suppress the generated ORDER BY clause with a comment notation.
    Note: If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
  2. A lookup table name or column names contains a reserved word. If the table name or any column name in the lookup query contains a reserved word, you must ensure that they are enclosed in quotes.
  3. Use parameters and variables. Use parameters and variables when you enter a lookup SQL override. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the SQL statement, or use a parameter or variable as the SQL query. For example, you can use a session parameter, $ParamMyLkpOverride, as the lookup SQL query, and set $ParamMyLkpOverride to the SQL statement in a parameter file. The designer cannot expand parameters and variables in the query override and does not validate it when you use a parameter or variable. The integration service expands the parameters and variables when you run the session.
  4. A lookup column name contains a slash (/) character. When generating the default lookup query, the designer and integration service replace any slash character (/) in the lookup column name with an underscore character. To query lookup column names containing the slash character, override the default lookup query, replace the underscore characters with the slash character, and enclose the column name in double quotes.
  5. Add a WHERE clause. Use a lookup SQL override to add a WHERE clause to the default SQL statement. You might want to use the WHERE clause to reduce the number of rows included in the cache. When you add a WHERE clause to a Lookup transformation using a dynamic cache, use a Filter transformation before the Lookup transformation to pass rows into the dynamic cache that match the WHERE clause.
    Note: The session fails if you include large object ports in a WHERE clause.
  6. Other. Use a lookup SQL override if you want to query lookup data from multiple lookups or if you want to modify the data queried from the lookup table before the Integration Service caches the lookup rows. For example, use TO_CHAR to convert dates to strings.

Related questions

0 votes
    State the differences between SQL Override and Lookup Override?...
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    What do you mean by lookup transformation?...
asked Dec 28, 2020 in Technology by JackTerrance
0 votes
    What are the different transformations where you can use a SQL override?...
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    What is meant by Lookup Transformation? Explain the types of Lookup transformation....
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    Differentiate between joiner and Lookup Transformation....
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    What is Lookup transformation?...
asked Mar 25, 2021 in Technology by JackTerrance
0 votes
    What do you understand by the record matching and data lookup features in ServiceNow?...
asked May 24, 2021 in Technology by JackTerrance
0 votes
    What do you understand by Dictionary Override?...
asked May 24, 2021 in Technology by JackTerrance
0 votes
    How Do You Override a Spring Boot Project’s Default Properties?...
asked Nov 15, 2020 in Technology by Editorial Staff
0 votes
    We have a requirement for a dexterity content type to have exclude from navigation behaviour but for the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 2, 2022 in Education by JackTerrance
0 votes
    Why is it that in some cases, SQL override is used?...
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    Why is it important to override GetHashCode when Equals method is overridden?...
asked Jan 16, 2021 in Technology by JackTerrance
0 votes
    I've noticed on Marshmallow (e.g. Nexus 6P) and also on some more recently updated Lollipop phones ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jul 20, 2022 in Education by JackTerrance
0 votes
    What is the use of Box-Cox transformation?...
asked Mar 11, 2021 in Technology by JackTerrance
0 votes
    Cryptographic algorithms are based on mathematical algorithms where these algorithms use ___________ for a secure ... Security:,Cyber Security-Jobs:,Cyber Security Applications...
asked Oct 31, 2021 in Education by JackTerrance
...