Solution for Informatica problems

                                                                                                                                                                       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.

                                                                                                                                                                       Scenario: Suppose you have a source is like this Source   E_NO    YEAR        DAYNO    ------   --------- -         ---------   1          01-JAN-07     301   2          01-JAN-08     200 Year column  is a  date and dayno is numeric that represents a day ( as in 365 for 31-Dec-Year). Convert the Dayno to corresponding year's month and date and then send to target. Target   E_NO           YEAR_MONTH_DAY    ------            --------- ----------      1                  29-OCT-07      2                  19-JUL-08 Solution: Use below date format in exp transformation   Add_to_date(YEAR,’DD’,DAYNO)

                                                                                                                                                                       Scenario: How to delete duplicate rows in a table? Solution: delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno); OR delete from emp a where rowid != (select min(rowid) from emp b where  a.empno=b.empno);

                                                                                                                                                                       Scenario: How to get nth max salaries ? Solution: select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);

                                                                                                                                                                       Scenario: How to get 3 Max & Min salaries? Solution: Max - select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc; Min - select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);

                                                                                                                                                                       Scenario: Find FIRST &  LAST n records from a table. Solution: First - select * from emp where rownum <= &n; Last - select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

                                                                                                                                                                       Scenario: Find the 3rd MAX & MIN salary in the emp table Solution: Max - select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal); Min - select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal >= e2.sal);

                                                                                                                                                                       Scenario:   Sql query to find EVEN & ODD NUMBERED records from a table. Solution: Even - select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp); Odd - select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);                                                                                                                                                                       

                                                                                                                                                                       Complex Queries in SQL ( Oracle )    To fetch ALTERNATE records from a table. (EVEN NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);    To select ALTERNATE records from a table. (ODD NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);    Find the 3rd MAX salary in the emp table. select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);    Find the 3rd MIN salary in the emp table. select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);    Select FIRST n records from a table. select * from emp where rownum <= &n;    Select LAST n records from a table select * from emp minus select *

                                                                                                                                                                       Complex Queries in SQL ( Oracle )    To fetch ALTERNATE records from a table. (EVEN NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);    To select ALTERNATE records from a table. (ODD NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);    Find the 3rd MAX salary in the emp table. select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);    Find the 3rd MIN salary in the emp table. select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);    Select FIRST n records from a table. select * from emp where rownum <= &n;    Select LAST n records from a table select * from emp minus select *

                                                                                                                                                                       Quiz: Informatica Set 2 A lookup transformation is used to look up data in Explanation: flat file Relational table view synonyms All of the above (correct) Which value returned by NewLookupRow port says that Integration Service does not update or insert the row in the cache? Explanation: 3 (wrong) 2 1 0 Which one need a common key to join? Explanation: source qualifier joiner (correct) look up Which one support hetrogeneous join? Explanation: source qualifier joiner (correct) look up What is the use of target loader? Explanation: Target load order is first the data is load in dimension table and then fact table. Target load order is first the data is load in fact table and then dimensional table. Load the data from different ta

                                                                                                                                                                       Quiz: Informatica Set 1 Which one is not correct about filter transformation? Explanation: Filter generally parses single condition. For multiple condition we can use router Act as a 'where' condition Can't passes multiple conditions Act like 'Case' in pl/sql (wrong) If one record does not match condition, the record is blocked Can we calculate in aggrigator ? Explanation: No Yes (correct) Which one is not a type of  fact? Explanation: Semi-aditive Additive Confirm fact Not additive (wrong)  Which one is not a type of dimension ? Explanation: Conformed dimension Rapidly changing dimension (correct) Junk dimension Degenerated dimension Which of these not correct about Code Page? Explanation: A code page contains encodin

                                                                                                                                                                       Scenario: How large is the database,used and free space? Solution: select     round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" ,                round(sum(used.bytes) / 1024 / 1024 / 1024 ) -                 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" ,                round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from     (select     bytes                 from       v$datafile                 union     all                 select     bytes                 from      v$tempfile                 union    all                 select    bytes                 from      v$log) used ,                (select sum(bytes) as p                 from dba_free_space) free group by free.p

                                                                                                                                                                       Scenario: Batch File to Append Date to file name Solution: @echo off REM Create a log file with the current date and time in the filename REM     the ~4 in the Date skips the first four characters of the echoed date stamp and writes the remainder and so on set LOG_FILE_NAME=Example_File_Name.%date:~4,2%%date:~7,2%%date:~10,4%.%time:~0,2%%time:~3,2%%time:~6,2%.txt Echo This is much easier in UNIX > c: emp\%LOG_FILE_NAME% :exit OR @echo off for /F "tokens=2,3,4 delims=/ " %%i in ('date/t') do set y=%%k for /F "tokens=2,3,4 delims=/ " %%i in ('date/t') do set d=%%k%%i%%j for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do set t=%%i%%j set t=%t%_ if "%t:~3,1%"=="_"