Select Expert use of Formula selecting ALL. with one drawback ???

[dreman]

CR 8.5 wiht SQL 2000.
I have a report that uses few parameters.
One of the parameter is to select supplier {?user_supplier} (Numeric) or enter 99 for to select ALL.
I do the following:
I go to the select expert, select formula:
(if {?user_supplier} = 99 then TRUE else {?user_supplier} = table.user_supplier)
it works fine (But too long to execute).
I would like to pass it to the SQL query. Any advice, Thank you.

dré

 

[Naith]

Database pass:

(
If {?User_Supplier} <> 99
Then {Table.User_Supplier) = {?User_Supplier}
Else
If {?User_Supplier} = 99
Then True
)

Naith

 

[HowardHammerman]

A simpler and perhaps quicker version:

(
If {?User_Supplier} <> 99
Then {Table.User_Supplier) = {?User_Supplier}
Else True
)
Howard Hammerman,
Crystal Reports training, consulting, books, training material,support. HammerLauch program runs reports without Crystal
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Naith]

If you do it like that, the parameter doesn't get passed to the database, Howard.

Try it out.

Naith

 

[rogar]

This also works and passes to the SQL:
({?User_Supplier}= 99 OR {Table.User_Supplier) = {?User_Supplier})
outer parens must be used if there is anything else in the Record Selection formula.

 

[Naith]

Rogar, that won't pass to the database either.

 

[rogar]

Naith,
It works for me, using CR8.5 and an Oracle 8.1 database. When &quot;{?User_Supplier}= 99&quot; evaluates to True, the SQL has no &quot;Table.User_Supplier&quot; in the WHERE clause; otherwise I get &quot;WHERE ... AND Table.User_Supplier = 1&quot; (when {?User_Supplier}=1).
I have numerous instances of this in my reports.

 

[dreman]

It will still not pass it to the SQL filter.
Would like to know a way to select all or range to be passed to the SQL filter. Thank you.
dré

 

[Naith]

That's really interesting, Rogar. I've only tested yours on Access and Sybase, and found Crystal was doing the monkey work. How are you connecting? Natively?

That goes for you too, Dreman. What's your db, and how are you connecting to it? Because the bracketed Double-If example should pass all the time. If possible, paste your Selection Criteria and generated SQL.

Naith

 

[rogar]

Naith
I'm using a native connection to Oracle. Have not tried it with other connections. I always ( all right, usually ) check to see if my record selection passes to the SQL, at least since reading all the recent discussions on this topic in tek-tips.

 

[Naith]

It's probably down to driver difference, as I'm connecting too all my databases via ODBC. I don't have the luxury of initiating a native connection here, but if you get the opportunity to try your syntax on an ODBC connection and post the results, you'd be The Man!

Naith

 

[dreman]

Naith:
Ok, I am using SQL 2000 with CR 8.5
I tried it under ODBC and SQL driver.
({?User_Supplier}= 99 OR {Table.User_Supplier) = {?User_Supplier}) does not get passed to SQL.
The formula works ok but it has to go to all the records (8 million records...) so it is very critical to pass it to SQL query.
Any advices, thank you.
dré

 

[Naith]

That's not what I said passes to the database.

If you're using ODBC, I said to use this:

(
If {?User_Supplier} <> 99
Then {Table.User_Supplier) = {?User_Supplier}
Else
If {?User_Supplier} = 99
Then True
)

Naith

 

[dreman]

Naith:
I tried with ODBC formula:
(
If {?User_Supplier} <> 99 Then
{Table.User_Supplier) = {?User_Supplier}
Else
True
)
via select expert, formula.
It still will not pass it to SQL query.
I even went to SQL query and reset. still will not work.
Please help thanks,
dré

 

[Naith]

Dreman...

I know that formula doesn't pass to SQL, which is why I never gave you it in the first place.

Just cut and paste either of my SQL posts...

Naith

 

[dreman]

Naith:
I was able to figured it out. It is simple yet tricky.
Do not use if statement, trick the formula in this manner:
select expert:

... these are other sql statement
...
-- include the following
(
{?User_Supplier} = 99 or
{?user_Supplier} = {Table.Supplier}
)
See if ?User_Supplier = 99 it will select all if not it will execute or and will select the user entry.
It works to the tee. It only took me few days to figure it out.
Thank for the help.
POst is closed.

dré

 

[synapsevampire]

An If will work for this too, and if the complexity of your record selection criteria changes, you may find that your method stops passing the SQL.

Naith provided the most reliable means for passing SQL, but there are exceptions, use whatever works, but check it whenever anything changes.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com