DSXchange

hi all

i have the below job

oracle transformenr oracle


the first stage has load date i need to put it to the next stage only if the sysdate is same as the loaddate if not dont load it.

how do i do this

i was thinking i will add a constraint but i cannot exit.

or i can reject rows after i compare the dates is there a date fucntion that will return a value

or is there some other way i can do this

thanks all

rxs0005

Hi rxs0005,
When you fetch data from the source ORACLE table itself you can use a where clause in the query which compares it with the sysdate and fetch only that data. You need not put any constraints in the transformer at all. This is the simplest way i can think of for this problem. Hope this solves your problem.

rxs0005 wrote:hi all

i have the below job

oracle transformenr oracle


the first stage has load date i need to put it to the next stage only if the sysdate is same as the loaddate if not dont load it.

how do i do this

u can do it by using stage variables in transformer.
take one stage variable (of Date type)...define its derivation as sysdate.
using If..then..Else ,
If loaddate=stg_sysdate then 1 else 0.......>store this returned value in another stage variable 'check'.

now give constraint as check=1.

this will solve ur problem..

and one note...try to avoid capturing rejected records into ur main data flow...always build the logic so that u r capturing only accepted records.
since rejected records wont be counted in case of 'Usage Analysis'.

guys

thanks for your inputs i will try both and see

ridar : how do i store the check stage variable do i just pass the 0/1 into it and do a isnull(check)


let me know

thanks

rxs0005

Your query can use a WHERE clause with the system date variable appropriate to your database to limit the rows out of the database. This is preferred because then those rows don't have to leave the database, traverse the network, and then be thrown away by DataStage.

The option of using a Constraint in a transformer will work only if your dates are in the same character format. So, DS will automatically put a TO_CHAR(yourdate, 'YYYY-MM-DD HH24:MI:SS') if using the Oracle OCI stage, otherwise you need to conform your date if using other stages/databases. The variable to use in your job for current date is OCONV(@DATE, "D-YMD[4,2,2]") where the D-YMD[4,2,2] represents whatever format you need to get the dates to look the same.

Now, if you need dates with time values, that's a whole different situation.

Kcbland

i am using the where clause but what is happening is the next stage is an OCI stage i do a truncate table before insert . So if the where clause is true that is the output of the first stage is 0 it outputs 0 rows but it also truncates the table i dont want that to happen if it does not load any rows.

how do i do that.

basically using the where clause if no rows satisfy the condition then the next stage should not truncate basically i need to prevent the next stage from happening.

thanks

rxs0005

Can't be done in one job. You'll need to do the extraction in one and then only run the second truncate-and-load conditionally on the first job actually producing output.

kcbland wrote: The option of using a Constraint in a transformer will work only if your dates are in the same character format. So, DS will automatically put a TO_CHAR(yourdate, 'YYYY-MM-DD HH24:MI:SS') if using the Oracle OCI stage, otherwise you need to conform your date if using other stages/databases. The variable to use in your job for current date is OCONV(@DATE, "D-YMD[4,2,2]") where the D-YMD[4,2,2] represents whatever format you need to get the dates to look the same.

Now, if you need dates with time values, that's a whole different situation.

kcbland,

thanks for explaining performance issues.