Hi All, CR8.5 on Oracle8i I'v

[Reebo99]

Hi All,

CR8.5 from Oracle8i

I've got 3 parameter fields and the selection formula has to evaluate the value of each field then select accordingly. My selection formula is :

if {?idtype} = &quot;All&quot; and {?id} <> &quot;All&quot; and {?Currency} <> &quot;All&quot; then {ACCOUNT} = {?id} and {CURRENCY_ID} = {?Currency} else
if {?idtype} = &quot;All&quot; and {?id} = &quot;All&quot; and {?Currency} <> &quot;All&quot; then {CURRENCY_ID} = {?Currency} else
if {?idtype} = &quot;All&quot; and {?id} <> &quot;All&quot; and {?Currency} = &quot;All&quot; then {ACCOUNT} = {?id} else
if {?idtype} <> &quot;All&quot; and {?id} = &quot;All&quot; and {?Currency} <> &quot;All&quot; then {ID_TYPE} = {?idtype} and {CURRENCY_ID} = {?Currency} else
if {?idtype} <> &quot;All&quot; and {?id} = &quot;All&quot; and {?Currency} = &quot;All&quot; then {ID_TYPE} = {?idtype} else
if {?idtype} <> &quot;All&quot; and {?id} <> &quot;All&quot; and {?Currency} = &quot;All&quot; then {ID_TYPE} = {?idtype} and {ACCOUNT} = {?id} else
if {?idtype} <> &quot;All&quot; and {?id} <> &quot;All&quot; and {?Currency} <> &quot;All&quot; then {ID_TYPE} = {?idtype} and {ACCOUNT} = {?id} and {CURRENCY_ID} = {?Currency} else
if {?idtype} = &quot;All&quot; and {?id} = &quot;All&quot; and {?Currency} = &quot;All&quot; then {ID_TYPE} = {ID_TYPE} and {ACCOUNT} = {ACCOUNT} and {CURRENCY_ID} = {CURRENCY_ID}

This works, but it's a bit messy. Could anyone help with a better way? (I know the last line looks like it doesn't need to be there, but for some reason it won't work without it). Cheers. Reebo
Scotland (Going mad in the mist!)

 

[Naith]

Hey Reebo,

That must have been pretty unpleasant for you to code - and probably as unpleasant for you to maintain 3 months down the line from now - because you're handling 3 wildcard possibilities with the same statement.

Try handling one parameter at a time, and see how you get on:

(
(If {?idtype} <> &quot;All&quot;
Then {ID_TYPE} = {?idtype}
Else
If {?idtype} = &quot;All&quot;
Then True)
and
(If {?id} <> &quot;All&quot;
Then {ACCOUNT} = {?id}
Else
If {?id} <> &quot;All&quot;
Then True)
and
(If {?Currency} <> &quot;All&quot;
Then {CURRENCY_ID} = {?Currency}
Else
If {?Currency} <> &quot;All&quot;
Then True)
)
...the rest of your formula here...

The parts in blue, you don't actually need to enter, as the formula will still work without it. It's just you may need to enter the blue parts to force this part of the SQL to do it's processing on the database.

All the best mate,

Naith

 

[Reebo99]

Naith,

Thanks for your help. I was trying to evaluate each one separately in an IF.THEN.ELSE but didn't know you had to put () around each to bring them together in the AND statement, that's how I got to this messy formula.

Once again, I bow down to your encyclopedic knowledge! Reebo
Scotland (Going mad in the mist!)

 

[Naith]

You don't need the parentheses per se - as you know, yours worked without any at all.

I just stick 'em in to increase the chances of Crystal passing each chunk of the criteria to the database.

Try it with and without brackets, check your SQL generated, and see what how your performance overhead is affected, and you may see what I mean.

Take it easy up there,

Naith

 

[Reebo99]

Naith,

Tried with and without brackets and it works both ways. I tried checking a backup version and the only thing missing was else true. What an absolute bugger!

Anyway, I went to Show SQL Query and nothing appears for the selection formula??!? Could this be because I'm using a Left Outer Join on 2 of my tables?

p.s. Taking it very easy at the moment, looks like I'm here till July now, hopfully back in the real world soon! Reebo
Scotland (Going mad in the mist!)