Using ODI variable

user12251389

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.

Rodrigo Radtke Souza

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

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.

user12251389

@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..

Rodrigo Radtke Souza

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?

user12251389

@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

Rodrigo Radtke Souza

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.

Rodrigo Radtke Souza

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:

user12251389

@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 :

Rodrigo Radtke Souza

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')

user12251389

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:

Rodrigo Radtke Souza

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.