Solution for Informatica problems

============================================================================================= SQL Transformation with examples   Use : SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline . We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation. Use SQL transformation in script mode to run DDL (data definition language) statements like creating or dropping the tables. The following SQL statements can be used in the SQL transformation. Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE) DATA Retrieval Statement (SELECT) DATA Control Language Statements (GRANT, REVOKE) Transaction Control Statements (COMMIT, ROLLBACK) Scenario: Let’s say we want to create a temporary table in mapping while workflow is running for some intermediate calculation. We can use SQL transformation

Efficient SQL Statements This is an extremely brief look at some of the factors that may effect the efficiency of your SQL and PL/SQL code. It is not intended as a thorough discussion of the area and should not be used as such. Check Your Stats Why Indexes Aren't Used Caching Tables EXISTS vs. IN Presence Checking Inequalities When Things Look Bad! Driving Tables (RBO Only) Improving Parse Speed Packages Procedures and Functions Check Your Stats The Cost Based Optimizer (CBO) uses statistics to decide which execution plan to use. If these statistics are incorrect the decision made by the CBO may be incorrect. For this reason it is important to make sure that these statistics are refreshed regularly. The following article will help you achieve this aim. Cost Based Optimizer (CBO) and Database Statistics Why Indexes Aren't Used The presence of an index on a column does not guarantee it will be

NVL2 The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter. The following query shows NVL2 in action. SQL> SELECT * FROM null_test_tab ORDER BY id;           ID COL1        COL2        COL3        COL4 ---------- ---------- ---------- ---------- ----------          1 ONE         TWO         THREE       FOUR          2             TWO         THREE       FOUR          3                         THREE       FOUR          4                        THREE       THREE   4 rows selected. SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;                  ID OUTPUT ---------- ----------                  1 TWO                  2 THREE                  3 THREE                  4 THREE   4 rows selected.   SQL> COALESCE The COALESCE function was introduced in Oracle 9i