Pushing optional parameter as sql

[spookster]

Still using CR7 - yes it's old but is adequate for us.

Having BIG problems persuading optional parameters as part of selection formula to be pushed to server side processing, ie it won't go in where clause of show sql query and thus does a lot of extra processing.

I have a parameter that can either be ALL or a selected record id (4 characters text).

I have exhausted all the known ways I have found on various forums, blogs, etc using loads of combinations of IF, ELSE, OR, AND, etc and it just refuses to work.

Any ideas?

I have tried;

if {?param}="ALL" then
true
else
{Table.Field} = {?param}

also tried;

{?param} = "ALL"
or
{Table.Field} = {?param}

also tried about another 10 different ways. It's my bet that CR7 is just too old and does not push as much as newer versions.

We are using Access databases via an ODBC link.

If anyone has knowledge, specially of CR7 and pushing these types of parameters to sql where command, then you will make my day.

 

[synapsevampire]

Check my FAQ here, especially helpful for older versions:

faq767-3825

Basically, try:

(
if {?param} <> "ALL" then
{Table.Field} = {?param}
else
if {?param} = "ALL" then
true
)

Note the use of parentheticals.

I'm always able to get it to pass, so don't give up.

-k

 

[spookster]

Thanks for the incredibly quick response!

I don't want to give up, but we have been using CR7 for many years now and we have never got it to work properly in this area. None of your suggestions work either I'm afraid. Simple parameters go in the where clause just nicely, but any sort of if, then, else, or, etc seems to preclude it. And yes, I've ticked all the boxes about using indexes and server for speed.

All the pages I've found so far talk about CR 8.5, 9, etc, not V7 so it's my betting that it wasn't implemented in such an old program.

Because I pass the parameters from a coldfusion script I have now found a workaround but it requires two parameters instead of 1, basically a 'from' value and a 'to' value and then I do this in selection;

{Table.Field} >= {?paramfrom}
and
{Table.field} <= {?paramto}

The trick is to pass it the same values if user selects a particular record, or if user wants all records, pass it a low ascii value, as 'from' parameter and a high ascii value as 'to' parameter. The formula then goes in where clause quite happily.

I do however have some more complicated fields that I wish to use if then structures on, so ideally a 'proper' method would be advantagous.

 

[synapsevampire]

Please post precisely what you tried.

I can't help you unless you supply technical information.

-k

 

[synapsevampire]

Oh, and please post EVERYTHING that is in the record selection formula(s).

-k

 

[spookster]

OK, have made a collage of the windows into one image file;

http://www.dbhut.com/images/cr7.gif

Have made the most basic report possible, which is one table, one field on the report, one parameter and the dreaded selection criteria. As you can see the SQL query does not show the where clause, even when I type a valid string into the parameter.

The table has about 200,000 records and I know it's not doing the where clause becuase report it relatively slow to run and I see it looping through all the records in the status bar. If I just put a straight selection that will go in sql query where clause, and I type a location that only has a few records from te table, then report is almost instant.

So, it's just these annoying optional parameters I'm trying to nail.

Once again, many thanks for your help. It's gone past midnight here in the UK, so I'll check up again on this forum in about another 9 hours or so!

 

[synapsevampire]

Interesting...

I can generally get MS Access to pass it as well, but this looks right.

Sometimes it takes some additional voodoo, but I have to leave for the day now. Does it speed up if you change the connectivity to direct? You can't see the SQL in that case, but it might pass the criteria correctly.

I'll thunk on it and post more tomorrow.

-k