not able to see my record selection in WHERE clause

[yehong]

Crystal Reports 8.5 with Oracle.
I am trying to push down a record selection formula to the database. However, the formula is not getting to the WHERE clause. My formula is very simple:
//@RecSelect
//two parameters, Param1={?Gender}, Param2={?Age}
If {?Gender}='Male' Then
Table1.Age >={?Age}
Else If {?Gender}='Female' Then
Table1.Age <={?Age}

I should the WHERE clause to say
WHERE
Table1.Age >=30 // in case of Male and Age=30

Any idea why I am not able to see it? If I just have on line in the record selection, like:
Table1.Age >={?Age}, it shows in the WHERE clause.

 

[lbass]

Try:

(
{?Gender}='Male' and
{Table1.Age} >={?Age}
) or
(
{?Gender}='Female' and
{Table1.Age} <={?Age}
)

-LB

 

[synapsevampire]

What connectivty are you using? Make sure you use either the Native connectivity (preferred) or the Crystal supplied ODBC driver for Oracle, not Oracles.

Is this the record selection in it's entirety, or are there other things? People luv holding back so that they can watch us geek-chimps tat-tat-tatting away...

Make the following the only thing in the Report->Edit Selection Formula->Record:

(
If {?Gender}= "Male" Then
Table1.Age >= {?Age}
Else If {?Gender}='Female' Then
Table1.Age <={?Age}
)

I have a FAQ which discusses pass through here:

faq767-3825

-k

 

[synapsevampire]

It should work either way, but good point, LB.

-k

 

[yehong]

The conncetivity is ODBC ,using Oracle in Orahome 92 driver.
The formula that I gave above is the only formula in my record selection, with exception to 'ToNumber' function that I am using on {?Age} parameter. So the formula is:
If {?Gender}= "Male" Then
Table1.Age >= ToNumber({?Age})
Else If {?Gender}='Female' Then
Table1.Age <= ToNumber({?Age})

I tried both of your formulae,LB's is working but it is creating a WHERE clause like:
Table1.Age >=30 Or
Table1.Age <=30, and that is causing problems when the report is run from the application. Any other idea?

 

[lbass]

Actually, I think my earlier suggestion should have been

(
{?Gender}='Male' and
{table.gender} = 'Male' and
{Table1.Age} >={?Age}
) or
(
{?Gender}='Female' and
{table.gender} = 'Female' and
{Table1.Age} <={?Age}
)

This would return only the gender selected by the parameter. If you mean to return males older than a certain age and females younger than a certain age, then you would change the formula to the following which eliminates the gender parameter:

(
{table.gender} = 'Male' and
{Table1.Age} >={?Age}
) or
(
{table.gender} = 'Female' and
{Table1.Age} <={?Age}
)

Not sure what your intention is.

-LB

 

[synapsevampire]

Why are you using a tonumber in the where clause?

Change the age to a number parm, this alwasy causes some heartache for Crystal.

The Oracle supplied ODBC driver is NOT suggested for use with Crystal.

And please don't post fake record selection formulas and ask why they don't work. People will supply an answer, and then you say "just kidding", here's what I'm really using.

Try:

(
If {?Gender}= "Male" Then
Table1.Age >= {?Age}
Else If {?Gender}='Female' Then
Table1.Age <={?Age}
)

Change the parm data type to a number and use the above, assuming that you want males over the age, or females if under the age.

That's one crazy formula though.

To speed up help, try posting technical information:

parameter*(s) (types included)
Example data (types included)
Expected output

-k