For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
I am using ODI 12c. I have created Variable and i am trying to use this variable value in filter condition to filter the data based on a timestamp. But its not working. Basically what i am trying is from my target table TEST_SDADDR i am trying to get max Timestamp everytime and trying to pass this value in Filter condition so that from source table CSL_STG_DEC_SDADDR, i will always load the latest data into my target table TEST_SDADDR. This is how i have created variable and trying to use Variable in filter: Step1. Step2. Step3. I am getting below error: ODI-1593: The following bind parameters (36:29.0) in the task command are not bound to any value. All the bind parameters should be bound for the command to be successful.
An advise from an experienced ODI developer. Never, ever, use "Date" as Datatype for a variable. It will only give you headaches. Create a variable as string and then convert to date wherever you needed it. Trust me, the headache of using Date datatype in ODI is not worth it. Hope it helps.
@rodrigo-radtke-souza thanks for the suggestion i have now changed the datatype to text and try to convert the date...its now working but not expectedly. In the source table i have also null values in Timestamp field and in the first run it has inserted the data into target table but without the rows which has null values in the Timestamp field from source table. Another issue is when i try with second run it have inserted duplicates in the target and in the third run again another duplicates. So its not even checking the condition in the filter condition it seems. Below are the change part and the rest are same: I am not sure using filter component is the right method. May be we have to use subquery component ? But not sure how it works..
Can you explain to me how are you running this mapping? Are you running it directly or inside of a package? From your description, it seems that you ran it directly, so the variable got the default value of 19000101 and loaded all records but the nulls. If you run the mapping directly again, you will reloaded all the rows again, since it will get the default value of 19000101 again. What you need to do is to create a package, put the variable as the first step, set it as "refresh" and then the mapping as the second step. In this way, the variable inside the mapping will get the updated value from the refresh command. Does that make sense?
@rodrigo-radtke-souza it actually makes sense...now i have tried the same way but somehow i dont understand why again its inserting duplicates in the table and also its not inserting the data which has null values for Timestamp field. I have also validate the query in the variable. I dont understand one thing. When i checked the code in operator logs, in the insert query the where condition looks like below: FROM TSTER WHERE (CSL_STG_DEC_SDADDR_1.TIMESTMP > #TST_PROJECTS.LATEST_TIMESTAMP )
I dont know why its not showing the date from variable value but its showing variable parameter : #TST_PROJECTS.LATEST_TIMESTAMP
Below are my steps: 1. 2. 3.Mapping 4.Package
If you want to see what value was used in that step, run the package with log = 6. Then go to operator, double click the step that you want to check and check the variables tab: About the nulls, you are not inserting them because you are testing TIMESTAMP bigger than the variable. Nulls will fail this test and will not be loaded. If you want to load the null values, you will need to add "OR TIMESTAMP IS NULL" in the filter.
I also noticed that you didn't put your variable as the first step to run in the package (the green triangule icon). Do the following: Right click the variable an select "First Step". The green triangule will show up there:
@rodrigo-radtke-souza as soon as i made variable as the first step to run in the package i am getting error in the Insert code as : ODI-1593: The following bind parameters (00:00.0) in the task command are not bound to any value. All the bind parameters should be bound for the command to be successful....i think the error might be in the Where clause...Do we have to make any changes here may be for mapping ?
Now as you mentioned i can see variable value :
Ok, so lets start from the beggining. Your variable is now text. Please refresh it to be text only. Do not add TO_DATE to the variable. You want just a plain test, as 19000101 for example. Its a text, not a date. Then, when you add the filter, do the text to date converstion there: TIMESTAMP>TO_DATE('#RDM_ETL_PROJECTS.TEST_TIMESTAMP','YYYYMMDD')
Now the error is not valid month. In database table TIMESTMP is of date datatype. This is how in operator logs Where condition looks like : WHERE (CSL_STG_DEC_SDADDR_1.TIMESTMP > TO_DATE('1900-01-01 00:00:00.0','YYYYMMDD') )
I made below changes:
Did you change the SQL in refresh variable? It also needs to be text. The last picture of your variable refresh was returning a date value. Change it to return a text with the pattern YYYYMMDD, not a date.
@rodrigo-radtke-souza Do you mean something like this or using to_char ? SELECT NVL(MAX(TIMESTMP), 19000101) FROM TSTER
SELECT NVL(TO_CHAR(MAX(TIMESTMP),'YYYYMMDD'), '19000101') FROM TSTER
@rodrigo-radtke-souza Thanks now its running...But there is small issue...In the first run it took the date as 19000101, in second run it took the correct max date as 2021-09-27, again in third run it took the max date as 2021-09-27. Now the problem is for every run there is one new duplicate row added in target table.. Normally during the second run it should check the max TIMESTMP from the target table and check filter condition where if the TIMESTMP value from source table is greater than max TIMESTMP from target table then only insert new row in target table. This is how my target table looks like now. I have sorted the TIMESTMP to desc: Below is how my source table where I have sorted the TIMESTMP to desc :
:) Just expand your date mask. Instead of using 'YYYYMMDD' you may use 'YYYYMMDD HH24:MI:SS'. This will account for hours/minutes/seconds as well.
@rodrigo-radtke-souza thanks now its working perfectly :)
@rodrigo-radtke-souza i have followed the steps and it has solved my problem.How can i make this variable to be used by more than one table in my schema