"if then" in select formula??

[pelajhia]

I have a report which passes a selected value from a table to a subreport.
Based on the value passed (wonlinkkey),
I would like to change the select statment in the subreport.
I have 'successfully' used the following formula in the select expert, but it runs really slowly:
if
{?Pm-HISTORY.WOLINKKEY}=1
then
ltrim({HISTORY.WONO}) = {?Pm-@InputToAllCaps}
else
{?Pm-HISTORY.WOLINKKEY} = {HISTORY.WOLINKKEY}

If I just have the second line ({?Pm-HISTORY.WOLINKKEY} = {HISTORY.WOLINKKEY}) in the select statement, it runs really fast, but doesn't pull back the right records for wonlinkkeys = 1.

I have attempted to edit the sql directly, too, but I don't know much about the variable declaration syntax and I'm getting various errors - is this a viable route to spend some time on?
Or, more generally, is there a GOOD way to do this?
Thanks in advance.

 

[lyanch]

I would rewrite your selection formula to something like:

if
{?Pm-HISTORY.WOLINKKEY}=1
then
{HISTORY.WONO} = {?Pm-@InputToAllCaps}
else if {?Pm-HISTORY.WOLINKKEY}<>1

{HISTORY.WOLINKKEY} = {?Pm-HISTORY.WOLINKKEY}

You need the else if to get crystal to send the SQL to the query.. I am not sure that I am adding all the statements you need for the select formula but you get the idea...

And thanks for letting me know that I am not crazy...

Lisa

 

[pelajhia]

Thanks to one and all for your patience and assistance! I believe I have figured out a work around using various tips from this lengthy discussion.
Here is the final select expert statement I am using:
({HISTORY.WOLINKKEY}>1 and
{HISTORY.WOLINKKEY}={?Pm-HISTORY.WOLINKKEY})
or
{HISTORY.WONO}={?Pm-HISTORY.WONO}

WHY THIS WORKS ANY BETTER THAN PREVIOUS VERSIONS IS BEYOND ME. However, here are the two where clause results, depending upon wolinkkey = 1 or not = 1. I really kind of enjoy the logic that gets created when the wolinkkey=1; not exactly perfect, but it gets the job done and runs really fast. Yeah!

(wolinkkey=1)

WHERE
HISTORY.&quot;HISTKEY&quot; = COSTXTRA.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTLABR.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTVEH.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTPART.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTTOOL.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTEQ.&quot;HISTKEY&quot; (+) AND
(HISTORY.&quot;WOLINKKEY&quot; > 1 AND
HISTORY.&quot;WOLINKKEY&quot; = 1 OR
HISTORY.&quot;WONO&quot; = ' 1476')

[\b](wolinkkey>1)[/b]

WHERE
HISTORY.&quot;HISTKEY&quot; = COSTXTRA.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTLABR.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTVEH.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTPART.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTTOOL.&quot;HISTKEY&quot; (+) AND
HISTORY.&quot;HISTKEY&quot; = COSTEQ.&quot;HISTKEY&quot; (+) AND
(HISTORY.&quot;WONO&quot; = 'FY2003F' OR
HISTORY.&quot;WOLINKKEY&quot; = 6205)


My horse might be purple, but it's not dead.
Thanks again everybody!