Using refresh variable on interface

561241

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

645818

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

SaravananRajavel

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

Guru Sankar

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

561241

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

561241

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.

Guru Sankar

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