Forum Stats

  • 3,839,498 Users
  • 2,262,498 Discussions
  • 7,900,978 Comments

Discussions

Categories

Using refresh variable on interface

561241
561241 Member Posts: 187
edited Oct 15, 2008 5:11AM in Data Integrator
I have a variable (myTestVariable) which is a refresh variable. I want to use this as the filter on an interface. How do I do that ?

I have set the filter as my_column_name > #myTestVariable. I ran the interface and see this exception

com.sunopsis.sql.SnpsMissingParametersException: Missing parameter
at com.sunopsis.sql.SnpsQuery.completeHostVariable(SnpsQuery.java)
at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(SnpSessTaskSqlI.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
at com.sunopsis.dwg.cmd.e.i(e.java)
at com.sunopsis.dwg.cmd.g.y(g.java)
at com.sunopsis.dwg.cmd.e.run(e.java)
at java.lang.Thread.run(Unknown Source)

Edited by: user558238 on Oct 15, 2008 1:23 AM
Tagged:
· Share on TwitterShare on Facebook

Answers

  • 645818
    645818 Member Posts: 11
    edited Oct 15, 2008 4:35AM
    Hi,

    --You Drag&Drop the column where you want to put filter and you make it like that : column>#myTestVariable--

    I have not seen the last version of your query.
    Your variable is probably not set (declare) or is null.

    Best Regards

    Edited by: Utopus on Oct 15, 2008 1:27 AM
    · Share on TwitterShare on Facebook
  • SaravananRajavel
    SaravananRajavel Member Posts: 109
    Hi-

    You can use your refresh variable into your interface filter by using #variablename.

    Step 1. Insert Filter in your source datastore of your interface(drag drop the column which you want to put filter)
    Step 2. Select the filter and enter command in the implementation box like this: sour_column in (#myTestVariable)

    Hope this will help you.


    Thanks,
    Saravanan Rajavel
    · Share on TwitterShare on Facebook
  • Guru Sankar
    Guru Sankar Member Posts: 1,165
    Hi,

    In the package use that Refresh Variable before your Interface.

    I mean, in the package drag and drop the variable and then from the OK of that to your interface.

    Thanks,
    G
    · Share on TwitterShare on Facebook
  • 561241
    561241 Member Posts: 187
    Thanks for the responses. What I have to do to set the value ? (Even when I have used the variable in the package still I get the same exception)

    Here are steps what I did.

    1. Created a variable named vMyTestVariable, datatype to AlphaNumeric, Action to latest value, set the proper schema and the following as the Select query
    select TO_CHAR(NVL(MAX(LAST_UPDATE_DATE) , TO_DATE('01-01-1800','DD-MM-YYYY') ) , 'DD:MM:YYYY:HH:MI:SS') from demo_emp_tgt

    2. On the interface, I created the filter on column my_column1 with the following as the Implementation
    My_Other_Table.ACTION_DATE > TO_DATE ( #vMyTestVariable , 'DD:MM:YYYY:HH:MI:SS')

    At this stage I tried executing the interface and it had thrown the above exception

    Now, I tried running it in package (thinking that direct interface execution may not work).

    3. Created a package, dragged and dropped the variable into package and then dragged and dropped the interface.
    flow is Variable -----ok-----> Interface

    4. Now tried executing the package but still I see the same exception.

    Did I miss any step here ?

    Thanks
    · Share on TwitterShare on Facebook
  • 561241
    561241 Member Posts: 187
    Found from an old post that the variable must be enclosed in single quotes
    2719228


    After setting it as My_Other_Table.ACTION_DATE > TO_DATE ( '#vMyTestVariable' , 'DD:MM:YYYY:HH:MI:SS') on the interface filter, it worked fine.
    · Share on TwitterShare on Facebook
  • Guru Sankar
    Guru Sankar Member Posts: 1,165
    edited Oct 15, 2008 5:11AM
    In the filter just give My_Other_Table.ACTION_DATE='#vMyTestVariable'( in single quotes) not My_Other_Table.ACTION_DATE > TO_DATE ( #vMyTestVariable , 'DD:MM:YYYY:HH:MI:SS')

    Edited by: user9977304 on Oct 15, 2008 2:41 PM
    · Share on TwitterShare on Facebook
This discussion has been closed.