mjbriggs03 (1/24/2012)
I literally just wrote a parameter that I wanted to allow the user to use OR not use, their choice. It was a range parameter for population figures where the user types in the range (non-queried). So, here's the syntax..((A."Population" BETWEEN (:MinPopulation) AND (:MaxPopulation)) OR ((:MinPopulation IS NULL) AND (:MaxPopulation IS NULL)))
Now, I checked off ALLOW NULL VALUES and the set the default values to NULL and it seems to be working just fine.
Oh, I'm working out of an Oracle database hence the reason you're seeing ':' instead of '@.'
Hope this is somewhat helpful
Mike
Hi Mike,
I noted that you are executing that query against an Oracle DB, but wanted to point out for others' sake that, while this syntax yields the desired results, it has some significant performance drawbacks in SQL Server queries. See this seminal blog post on the topic:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Does Oracle handle this kind of query syntax differently?
Regards,
Jason
I noted your comment that you are executing that query against an Oracal
Jason Wolfkill