This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Using ODI variable

user12251389
user12251389 Member Posts: 335 Blue Ribbon
edited Oct 7, 2021 8:38PM in Data Integrator

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.

Best Answer

  • Rodrigo Radtke Souza
    Rodrigo Radtke Souza Los Angeles, CAMember Posts: 216 Gold Badge
    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.

    user12251389Marco Fris
«1

Answers

  • Rodrigo Radtke Souza
    Rodrigo Radtke Souza Los Angeles, CAMember Posts: 216 Gold Badge
    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.

    user12251389Marco Fris
  • user12251389
    user12251389 Member Posts: 335 Blue Ribbon
    edited Oct 7, 2021 7:00AM

    @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
    Rodrigo Radtke Souza Los Angeles, CAMember Posts: 216 Gold Badge

    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
    user12251389 Member Posts: 335 Blue Ribbon

    @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
    Rodrigo Radtke Souza Los Angeles, CAMember Posts: 216 Gold Badge

    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
    Rodrigo Radtke Souza Los Angeles, CAMember Posts: 216 Gold Badge

    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
    user12251389 Member Posts: 335 Blue Ribbon
    edited Oct 7, 2021 6:07PM

    @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
    Rodrigo Radtke Souza Los Angeles, CAMember Posts: 216 Gold Badge

    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
    user12251389 Member Posts: 335 Blue Ribbon
    edited Oct 7, 2021 6:40PM

    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
    Rodrigo Radtke Souza Los Angeles, CAMember Posts: 216 Gold Badge

    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.