Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Using refresh variable on interface

561241
Member Posts: 187
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
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
Answers
-
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 -
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 -
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 -
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 -
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. -
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
This discussion has been closed.