DSXchange

@Arnd

The Actual data type of AGENT_ID is Number [10,0] in oracle and in datastage i have mentioned as Bigint.
No ware in Orcale and datastage contains Decimal datatype.

Query:- SELECT a.ABCDEFGHIJ , Cast(COUNT(a.AGENT_ID) as Integer) AS TOT_AGENT_ID FROM TABLE_AAAA a LEFT OUTER JOIN TABLE_CCCCC c on a.AGENT_ID=c.AGENT_ID WHERE a.XYZ <= TO_DATE('#p_Rundate#','YYYY-MM-DD') - 1 AND a.ZYX >= TO_DATE('#p_Rundate#','YYYY-MM-DD') - 1 AND Trim(c.ABC)='XXXXXX' GROUP BY a.AB...

you mean to say, there is a SQL query stored in a table and you are using SQL stage to extarcing this column (sql query) and this query is passed to Transformer stage. In Transformer stage you want to replace the Where conditon Varible with values from parameter.

Is what your requirement?

Create a jobs as below Job 1: 1. First get the unique records of the two columns from actual table and have it in two files (COL1.txt) and (COL2.txt) Job 2: 1. Get the count of the records from (COL1.txt) and (COL2.txt) and load it into COUNT.txt file. Count of (COL1.txt) and (COL2.txt) should be eq...

Could you please little brief about your requirement Which stage you are performing sql query and where do you want to pass the parameter to the "where" clause. In general SQL query will be written in any database stages, but you want to pass the parameter from stagevariable. But stagevari...

@satyanarayana.. For creating the Header Record, first i need to load the detal data into XYZ.txt file and then need to take the count of the detail records from XYZ.txt and again this count record need to insert first record (Header) into a XYZ.txt file. Could plese read the top post's of my requir...

As I understood your requirement: In Lookup stage Primary Link you have column like XYZ and reference link is having col1 and col2. I guess you have done the mapping as below in Lookup stage. 1. Your join/lookup key is primary.XYZ = reference.col1. 2. Your target fields are tarcol1 and lkpval. 3. Yo...

@ Franklin Could you please elaborate or post your subroutine, how to insert a record at the front of an existing file My brief requirement: There is a file called XYZ.txt with size of 40 to 60 GB of data and this file having detail and trailer part. I need to insert a header record into the existin...

@Chulett I am clear up to loading the detail records into a file XYZ.txt (Header record will have before job script and it will be overwrite mode) with append mode in a same job. Do you mean to state like, need to create one more job to calculating the trailer record and then cat the trailer record ...

Please see the below job design Source(Seqfile) | | | CopyStage--->Trans1---->Target Seq File1(XYZ.txt) | | | Trans2 | | | Target Seq File2(XYZ.txt) In the above scenario I want to control a data flow like, first need to trigger the Trans1 stage and then trigger the Trans2 stage in the same job. Bot...

Having 4 load in the same job will not improve performance.

But for performance have 4 loads in 4 different jobs and in the sequencer make them to run in parallel. This will improve the performance a lot.

I have a solution but it will in terms of server job. Change it to have it in terms of Px Create a dummy hash file somewhere. In this job have the hash file as lookup as well as update stage. The hash file will have claim_id as key and 3 extra columns like VAL_00_FG VAL_01_FG VAL_OTH_FG Update the v...

If you are not using a transformer in between the Oracle & Lookup stage, then have a transformer and try it.

Were you able to view thw data atleast.

Will the type come in order like 20 followed by 23 followed by 24 or for the same id 20,23& 24 can come in any order.