Skip to Main Content

Analytics Software

Announcement

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.

Using ODI variable

user12251389Oct 6 2021 — edited Oct 7 2021

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.
image.png
Step2.
image.png
Step3.
image.pngI 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.

This post has been answered by Rodrigo Radtke Souza on Oct 6 2021
Jump to Answer

Comments

Answer

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.

Marked as Answer by user12251389 · Oct 7 2021
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:
Capture.PNG
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.
image.pngDoes 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.
image.png
2.
image.png
3.Mapping
image.png
4.Package
image.png

If you want to see what value was used in that step, run the package with log = 6.
image.pngThen go to operator, double click the step that you want to check and check the variables tab:
image.pngAbout 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:
image.pngRight click the variable an select "First Step". The green triangule will show up there:
image.png

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 ?

image.png
Now as you mentioned i can see variable value :
image.png

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:
image.pngimage.png

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.
image.pngChange it to return a text with the pattern YYYYMMDD, not a date.

user12251389

@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

user12251389

@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:
image.png
Below is how my source table where I have sorted the TIMESTMP to desc :
image.png

:) 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.

user12251389

@rodrigo-radtke-souza thanks now its working perfectly :)

Gladys Cheruiyot

@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

1 - 16