Half records to one target another Half to other..

--  In order to achieve this.. we need to design two pipelines..


-- In the first pipeline override the SQL Query as

  SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, EMP.HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO
FROM
 EMP WHERE ROWNUM < = (SELECT COUNT(*) /2 FROM EMP )

-- In the Second pipeline override the SQL Query as

  

SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, EMP.HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO
FROM
 EMP
MINUS
SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, EMP.HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO
FROM
 EMP WHERE ROWNUM <=(SELECT COUNT(*)/2 FROM EMP)

 -- Ram

I want to skip first 5 rows to load into target ?

> If your Source is a Flatfile and if you need to skip first 5 rows ...simply while Importing flatfile definition,

   set the properties as..Start import rows at option=6 .. generally 1 row goes as field names..so

   Start Import Rows at : 6

>  If  your source is a RDBMS, Override default SQL query as..

  SELECT * FROM EMP

  MINUS

  SELECT * FROM EMP WHERE rownum <=5

  so that records from 6 to n will be extracted by SQ..

> Other way..

   get the seq numbers using Seq-Gen-Tr.

   and in the Filter trasformation put condition as : seq_num > 5 .

   so only records from 5 - n records will be loaded into the target.

 

While performing incremental reading using mapping variable using setvariable how can i increment date value ,can  u  explain in detail,which function i have to use ?

> Generally Incremental reading will be done using Dates comparisions.

src_date_field > $$Mapping_var_date  ... etc.. to load only recent records.. etc..

> Our sample senario is ... Wee need to extract the records .. ' whose update/insert date is to be 3 days before from Todays date '..

so i have set the mapping variable as...

       SETVARIABLE($$Date_To_Extract, ADD_TO_DATE(SYSDATE,'DD',-3))

- here $$Date_To_Extract is Mapping Variable...

-- Ram

Even Records - Odd Records Loading..

How to load odd values in one target  and even values another, if my source is rdbms and for flat files?

If your Source is RDBMS

Design two piplines with different targets as you wish.. order of ports should be same..

In SQ transformation Override the query as...

In first pipeline SQ

-- SELECT * FROM EMP WHERE (ROWID,0) IN (SELECT ROWID,MOD(ROWNUM,2) FROM EMP)

In second pipeline SQ

-- SELECT * FROM EMP WHERE (ROWID,1) IN (SELECT ROWID,MOD(ROWNUM,2) FROM EMP)

If your Source is a Flat file..

Generate Seq Nos using Seq-Tr by deriving new port in Exp-Tr.. 

Create Router group..create one group.

> Group1   --   mod(seq_no,2) != 0  -- connect to Even records Table

> Default Group  -- connect to Odd Records Table...

- Ram

Dynamically Changing Flatfile Name

How to change Target file name with session run time

If your Infa is 8.x later ver.. then you can go ahead as Swetha suggested..
> Add 'FileName' port to your target definition as a new port.
> and set  the Date Format setting for that Port in Properties..
> You can Derive one o/p port in Exp-tr with correct datatype and precision
> O_FileName :  'Tgt_File_'||TO_CHAR(SESSSTARTTIME,'DD/MM/YYYY HH24:MI')||'.csv'
Here Instead of using  'Sysdate' better to use SESSSTARTTIME .

Another way is....

> In the Workflow create a Workflow variable $$Se_End_Time
> In the Data Flow.. create Session task and Assignment Task .. and connect them
> In the assignment task / Expressions /
$$Se_End_Time   =  TO_CHAR($Session_Name.EndTime,'DD-MM-YYYY')

> Create a Parameter file
[Folder_Name.WF:wkf_Name.ST:session_Name]
$FileName=Tgt_File_
$DatePart=$$Se_End_Time

In the above file ..
> $$Se_End_Time is the Workflow variable....

In the Session Properties...
> Output FileName :  $FileName$DatePart .csv
> and set the parameter file path ...


-- Ram

Working With Decision Task

How can I execute part of a daily workflow only on Sundays using a decision task?

> Put a decision task just before the main session which needs to be run only on Sunday.
> In the decision task, give the condition as to_char(sysdate,'DAY')='SUNDAY'.
> In the Link task to the downstream session, give the
$Decision_Task.Condition = TRUE and $Decision_Task.Status = SUCCEEDED.

Java Transformation Example

Converting One Row into Multiple Rows...Using Java Transformation..

Cosider my Source is a Flatfile and the data is like

ORDER_NO,ORDER_NAME,CHAIN

101,SOAP,4

102,CREAM,3

103,SHAMPOO,2

104,OIL,1

Now i want my target as..

 Target

ORDER_NO,ORDER_NAME,CHAIN

101 SOAP 4

101 SOAP 4

101 SOAP 4

101 SOAP 4

102 CREAM 3

102 CREAM 3

103 SHAMPOO 2

103 SHAMPOO 2

104 OIL 1

I am going to achieve this using Java-Transformation

> Create Java Tranformation and create InputGrooup and OutputGroup with ports as shown in diagram.

> Connect the ports from SQ to Exp-Tr(not needed) and then to InputGroup of Java-Tr.

> Open Java-Tr properties and chose Java Code tab.. and write the code as below..

> Compile the Java code for any errors..once code gets Successfully compiled..create session and workflow..and run the session..observ the output ..

-- Ram