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
load date compare
Moderators: chulett, rschirm, roy
Re: load date compare
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.
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.
Re: load date compare
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'.
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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
load date
kcbland,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.
thanks for explaining performance issues.