We have a need to do a select query. The "WHERE" of the SQL statement

scotte over 9 years ago

We have a need to do a select query. The "WHERE" of the SQL statement will need to be dynamic.The reason is that this is a "search" that our users are doing. They may select to search on 1 to 5 fields. In other words, the columns in our where clause will vary depending on the user situation. When using the Query Database smart node, we are struggling to find a way to accomplish this. Is there a way to get this done? Thanks!...

Discussion posts and replies are publicly visible

Parents

0 Chris over 9 years ago

Ah yes, in my use case we do not have null situations. LIKE will not return records with null values. To work around that, instead of "Column4 LIKE ac!col4Filter" you should be able to use:

Oracle: COALESCE(Column4, ' ') LIKE ac!col1Filter
SQL: IsNull(Column4, ' ') LIKE ac!col1Filter

.. with your ac! input for col1Filter set to: ="%" & pv!Col1Filter & "%"
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Chris over 9 years ago

Ah yes, in my use case we do not have null situations. LIKE will not return records with null values. To work around that, instead of "Column4 LIKE ac!col4Filter" you should be able to use:

Oracle: COALESCE(Column4, ' ') LIKE ac!col1Filter
SQL: IsNull(Column4, ' ') LIKE ac!col1Filter

.. with your ac! input for col1Filter set to: ="%" & pv!Col1Filter & "%"
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data