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

Great! On your form or search selection, you can set whatever is entered to variables (normal text, no '%'). Then on the ac! Data Inputs for the Query DB node, set as:

="%" & pv!col1Filter & "%"

Where pv!col1Filter is the exact text you would like to filter on, surrounded by 2 "%" signs.

COALESCE() takes 2 arguments and returns the first that is not null - so if the row is null, it will return whatever you enter as the second argument (basically replacing null values during the search) - or it will return the actual data. I do use a space in ' ' for text data. I have tried no space and get better results with a space. Also, for other data types you can use convert to string, such as "COALESCE(to_char(Column3),' ').
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Chris over 9 years ago

Great! On your form or search selection, you can set whatever is entered to variables (normal text, no '%'). Then on the ac! Data Inputs for the Query DB node, set as:

="%" & pv!col1Filter & "%"

Where pv!col1Filter is the exact text you would like to filter on, surrounded by 2 "%" signs.

COALESCE() takes 2 arguments and returns the first that is not null - so if the row is null, it will return whatever you enter as the second argument (basically replacing null values during the search) - or it will return the actual data. I do use a space in ' ' for text data. I have tried no space and get better results with a space. Also, for other data types you can use convert to string, such as "COALESCE(to_char(Column3),' ').
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data