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

                                                                                                                                                                       Scenario: Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose. Solution: After performing the below solution steps your end workflow will look as follows: START => SESSION1 => ASSIGNMENT TASK => SESSION2 SOLUTION STEPS SESSION1 This session is used to achieve your actual business logic. Meaning this session will perform your actual data load. It can be anything File Table. à File or Table à Table, File à WORKFLOW VARIABLES Create the following workflow variables. => $$Workflowname => $$SessionStartTime => $$SessionEndTime => $$TargetSuccessrows => $$TargetFailedRows ASSIGNMENT TASK Use the Expression tab in the Assignment Task and assign as follo

                                                                                                                                                                       Scenario: I want to use mapping parameter to store target file path. My question is can define file path in parameter file? If possible can anyone explain how to assign target file path as parameter? Solution: You can define the file path in parameter file. $OutputFileName=your file path here Give the above mentioned parameter in your parameter file.

                                                                                                                                                                       Scenario: Insert and reject records using update strategy. There is an emp table and from that table insert the data to targt where sal<3000 and reject other rows. Solution: 1.  connect out-puts from SQF to Update Strategy transformation. 2.  In properties of  Update Strategy write the condition like this             IIF(SAL<3000,DD_INSERT,DD_REJECT) 3. Connectthe Update Strategy to target

                                                                                                                                                                       Scenario: Suppose you are importing a flat file emp.csv and hire_date colummn is in numeric format, like 20101111 .Our objective is convert it to date,with a format 'YYYYMMDD'.   Source   EMPNO       HIRE_DATE(numeric)            -------            -----------      1                20101111      2                20090909 Target EMPNO            HIRE_DATE (date)          ------                   -----------      1                   11/11/2010      2                    09/09/2009 Solution: Connect SQF to an expression. In expression make hire_date as input only and make another port hire_date1 as o/p port with date data type. In o/p port of hire_date write condition like as below           TO_DATE(TO_CHAR(hire_date),’YYYYMMDD’)

                                                                                                                                                                       Scenario: How to change a string to decimal with 2 decimal places in informatica? Eg:: input data 12345678 I want output as 123456.78 Solution: output = to_decimal(to_integer(input)/100,2) OR SUBSTR(INPUT_FIELD, 1, LENGTH(INPUT_FIELD) - 2) || '.' || SUBSTR(INPUT_FIELD, -2)

                                                                                                                                                                       Scenario: I have the flat file in our server location; I want to append the data in a flat file for a daily run. Solution: We have an option in Informatica "Append if exists" in target session properties.