Dynamic Filtering?

[paross1]

Maybe something like this?

SELECT your_columns
FROM your_table
<cfif selected_state NEQ 0>WHERE State_ID = #selected_state # </cfif>

[Novian]

Hi Paross1,

Thanks for the reply. I'll give it a try. Is the idea that only the IF
statements that evaluate true will be executed? What about stringing together
the IF statements that return true? For example:

SELECT your_columns
FROM your_table
<cfif selected_state NEQ 0>WHERE State_ID = #selected_state # </cfif>
<cfif selected_country NEQ 0>WHERE Country_ID = #selected_country # </cfif>
<cfif selected_region NEQ 0>WHERE Region_ID = #selected_region # </cfif>

or would I write
SELECT your_columns
FROM your_table
<cfif selected_state NEQ 0>WHERE State_ID = #selected_state # </cfif> AND
<cfif selected_country NEQ 0>WHERE Country_ID = #selected_country # </cfif> AND
<cfif selected_region NEQ 0>WHERE Region_ID = #selected_region # </cfif>

Thanks again.

[rmaglies]

You can structure the where clause as follows to make the 'ands' easier

SELECT your_columns
FROM your_table
Where 1=1
<cfif #selected_state# NEQ 0>and State_ID = #selected_state # </cfif>
<cfif #selected_country# NEQ 0>and Country_ID = #selected_country # </cfif>
<cfif #selected_region# NEQ 0>and Region_ID = #selected_region # </cfif>

the 1=1 is always true, so any number of 'anded' where clauses can be added
after that

This way the 'ALL' selection can be used in any of the fields and the query
will return the correct result

[Novian]

Originally posted by: rmaglies
You can structure the where clause as follows to make the 'ands' easier

SELECT your_columns
FROM your_table
Where 1=1
<cfif #selected_state# NEQ 0>and State_ID = #selected_state # </cfif>
<cfif #selected_country# NEQ 0>and Country_ID = #selected_country # </cfif>
<cfif #selected_region# NEQ 0>and Region_ID = #selected_region # </cfif>

the 1=1 is always true, so any number of 'anded' where clauses can be added
after that

This way the 'ALL' selection can be used in any of the fields and the query
will return the correct result

Hey rmaglies,

Yeah, that's what I ended up doing although I didn't use the 'Where 1=1'. Is
this necessary? If so, why?

I really appreciate your input as well as that of paross1. I've been learning
so much via members like you and books from guys like Ben Forta.

Thanks again.

Novian

[paross1]

Look at what happens if you don't include WHERE 1 = 1 and all three of your
variables happen to equal 0 at the same time. You would have a query with an
empty WHERE clause, which will throw a database error. Including the 1=1 it
causes two behaviors, it prevents an error if your parameters are all 0, and
your query returns all rows (kind of a default ALL).

Phil