"if then" in select formula??

pelajhia · Feb 28, 2003

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.

synapsevampire · Feb 28, 2003

I'd guess that the SQL isn't getting passed because of the ltrim().

Try creating a SQL Expression to trim the field for you and then use the trimmed field in the comparison.

I would have provided an example were you to have shared the database type, version, and version of Crystal.

-k

IdoMillet · Feb 28, 2003

The use of a function (ltrim()) in your record selection condition causes the filtering process to occur on the PC rather than in the Database server.

Instead of
ltrim({HISTORY.WONO})
create and use an equivalent "SQL Expression Field" (the node below "Formula Fields&quot

.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

IdoMillet · Feb 28, 2003

S.V., next time you get ready to answer a question, give me a call and let me know you're "on the case"...

) CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

synapsevampire · Mar 1, 2003

You take every post number that's divisable by a prime, I'll take the rest...

-k

IdoMillet · Mar 1, 2003

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

pelajhia · Mar 3, 2003

Thanks for the responses. I have tried this same formula using the standard trimleft function instead of ltrim, but it did not run any better or worse. :|
I just now tried IdoMillet's idea, as I really feel this is the problem, but I have not found that switching to a sql expression has helped, either.
Here is how I tried to use the sql expression:
if
{?Pm-HISTORY.WOLINKKEY}>1
then
{HISTORY.WOLINKKEY} = {?Pm-HISTORY.WOLINKKEY}
else
{%TrimWONO}= {?Pm-@InputToAllCaps}

TrimWONO has the following inside it:
ltrim(HISTORY."WONO&quot

Am I using the sql expression idea incorrectly?
THanks for all the help.

IdoMillet · Mar 3, 2003

Try:
-------------------------------------------
({?Pm-HISTORY.WOLINKKEY}>1 AND
{HISTORY.WOLINKKEY} = {?Pm-HISTORY.WOLINKKEY})
OR
({?Pm-HISTORY.WOLINKKEY}<=1 AND
{%TrimWONO}= {?Pm-@InputToAllCaps})
-------------------------------------------

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

pelajhia · Mar 3, 2003

I have tried the suggested formula, which looks a lot nicer, but doesn't seem to run any faster. It does return the correct records after 3 minutes or so, but I'm back to my original problem of pretty darn slow processing.

For comparison, if I just use the first part of the select, ({?Pm-HISTORY.WOLINKKEY}>1 AND
{HISTORY.WOLINKKEY} = {?Pm-HISTORY.WOLINKKEY})
it takes about 5 seconds to run. (Tried this alternately with the 2nd part of the 'or' by itself, and it runs very quick, too). It just seems so unfair!

Also, I realized I don't need the lefttrim/allcaps functions afterall, and am still experience sloooooow processing.

Should I be doing this in a stored procedure, or is there still hope within crystal...? I kind of wondered if splitting this into 2 subreports would help, but I think it would not easily work around the problem.
Thanks for all the help!

synapsevampire · Mar 3, 2003

There's hope in Crystal, but you should post what is being generated in the SQL (database->Show SQL Query, and the current record selection formula in it's entirety.

I can almost always get the SQL to pass, but then again, if your database isn't properly indexed, there's little help.

Stored Procedures are always faster, though the difference is dependent upon the number of rows, indexes, statistics, etc.

-k

pelajhia · Mar 3, 2003

If I run this with a work order whose wolinkkey = 1,
I get:

SELECT
HISTORY."ADDDTTM", HISTORY."HISTKEY", HISTORY."WOLINKKEY", HISTORY."WONO",
COSTXTRA."COSTKEY", COSTXTRA."HISTKEY", COSTXTRA."TOTCOST",
COSTLABR."COSTKEY", COSTLABR."HISTKEY", COSTLABR."TOTCOST",
COSTVEH."COSTKEY", COSTVEH."HISTKEY", COSTVEH."TOTCOST",
COSTPART."COSTKEY", COSTPART."HISTKEY", COSTPART."TOTCOST",
COSTTOOL."COSTKEY", COSTTOOL."HISTKEY", COSTTOOL."TOTCOST",
COSTEQ."COSTKEY", COSTEQ."HISTKEY", COSTEQ."TOTCOST",
ltrim(HISTORY."WONO&quot

FROM
"IMSV7"."HISTORY" HISTORY,
"IMSV7"."COSTXTRA" COSTXTRA,
"IMSV7"."COSTLABR" COSTLABR,
"IMSV7"."COSTVEH" COSTVEH,
"IMSV7"."COSTPART" COSTPART,
"IMSV7"."COSTTOOL" COSTTOOL,
"IMSV7"."COSTEQ" COSTEQ
WHERE
HISTORY."HISTKEY" = COSTXTRA."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTLABR."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTVEH."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTPART."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTTOOL."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTEQ."HISTKEY" (+) AND
(HISTORY."WOLINKKEY" = 1 OR
HISTORY."WONO" = ' 1015')
ORDER BY
HISTORY."WOLINKKEY" ASC,
HISTORY."WONO" ASC,
HISTORY."HISTKEY" ASC

If I run it with a wolinkkey > 1, I get:

SELECT
HISTORY."ADDDTTM", HISTORY."HISTKEY", HISTORY."WOLINKKEY", HISTORY."WONO",
COSTXTRA."COSTKEY", COSTXTRA."HISTKEY", COSTXTRA."TOTCOST",
COSTLABR."COSTKEY", COSTLABR."HISTKEY", COSTLABR."TOTCOST",
COSTVEH."COSTKEY", COSTVEH."HISTKEY", COSTVEH."TOTCOST",
COSTPART."COSTKEY", COSTPART."HISTKEY", COSTPART."TOTCOST",
COSTTOOL."COSTKEY", COSTTOOL."HISTKEY", COSTTOOL."TOTCOST",
COSTEQ."COSTKEY", COSTEQ."HISTKEY", COSTEQ."TOTCOST",
ltrim(HISTORY."WONO&quot

FROM
"IMSV7"."HISTORY" HISTORY,
"IMSV7"."COSTXTRA" COSTXTRA,
"IMSV7"."COSTLABR" COSTLABR,
"IMSV7"."COSTVEH" COSTVEH,
"IMSV7"."COSTPART" COSTPART,
"IMSV7"."COSTTOOL" COSTTOOL,
"IMSV7"."COSTEQ" COSTEQ
WHERE
HISTORY."HISTKEY" = COSTXTRA."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTLABR."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTVEH."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTPART."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTTOOL."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTEQ."HISTKEY" (+) AND
(HISTORY."WOLINKKEY" = 6205 OR
HISTORY."WONO" = 'FY2003F')
ORDER BY
HISTORY."WOLINKKEY" ASC,
HISTORY."WONO" ASC,
HISTORY."HISTKEY" ASC

I just noticed also, that the 2nd run through did go pretty quickly - where the wolinkkey = 6205.
To more fully describe the data, the work orders with wolinkkey= 1, are not linked, and those with wolinkkey >1 are linked, via the wolinkkey. So, if the wolinkkey is 1, I want to only pull based on wono, but if >1, I want to pull any wono's with matching wolinkkeys. I guess maybe there are just a TON of wolinkkeys = 1...?
Please let me know if you can make some sense of this! Thanks.

synapsevampire · Mar 3, 2003

Note that the first pass had:

HISTORY."WONO" = ' 1015'

where the column wasn't trimmed, which may prove incorrect.

Also, you still haven't shared your record selection formula, just fragments of it, please post it.

-k

pelajhia · Mar 3, 2003

oops - here it is:
({?Pm-HISTORY.WOLINKKEY}>1 and
{HISTORY.WOLINKKEY}= {?Pm-HISTORY.WOLINKKEY})
or
({?Pm-HISTORY.WOLINKKEY}=1 and
{HISTORY.WONO}={?Pm-HISTORY.WONO})

pelajhia · Mar 3, 2003

also, the auto generated numbers-only work order numbers do have spaces in front (arg!) - that is why I mistakenly thought I needed the ltrim/trimleft function in the beginning. But then I realized I don't have to match to the user's input, since I get the actual wono out of the database during the main report's select statement. So I'm just directly comparing the wono from the main report with what i want to see in the subreport. Hope this is correct assumption.

lyanch · Mar 3, 2003

I have looked through your Selection formula 5 times.. and can't see how you get the SQL that you do if that is indeed your selection formula...

Either I am going batty (which is entirely possible) or you have changed your selection formula since you listed the SQL statements..

Lisa

pelajhia · Mar 3, 2003

I really have not (unless I blacked out or something

. I will say that the two sql statments I show are in reverse order of how I have the record selection formula.

Here, I have just run it again:

SELECT
HISTORY."ADDDTTM", HISTORY."HISTKEY", HISTORY."WOLINKKEY", HISTORY."WONO",
COSTXTRA."COSTKEY", COSTXTRA."HISTKEY", COSTXTRA."TOTCOST",
COSTLABR."COSTKEY", COSTLABR."HISTKEY", COSTLABR."TOTCOST",
COSTVEH."COSTKEY", COSTVEH."HISTKEY", COSTVEH."TOTCOST",
COSTPART."COSTKEY", COSTPART."HISTKEY", COSTPART."TOTCOST",
COSTTOOL."COSTKEY", COSTTOOL."HISTKEY", COSTTOOL."TOTCOST",
COSTEQ."COSTKEY", COSTEQ."HISTKEY", COSTEQ."TOTCOST",
ltrim(HISTORY."WONO&quot

FROM
"IMSV7"."HISTORY" HISTORY,
"IMSV7"."COSTXTRA" COSTXTRA,
"IMSV7"."COSTLABR" COSTLABR,
"IMSV7"."COSTVEH" COSTVEH,
"IMSV7"."COSTPART" COSTPART,
"IMSV7"."COSTTOOL" COSTTOOL,
"IMSV7"."COSTEQ" COSTEQ
WHERE
HISTORY."HISTKEY" = COSTXTRA."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTLABR."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTVEH."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTPART."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTTOOL."HISTKEY" (+) AND
HISTORY."HISTKEY" = COSTEQ."HISTKEY" (+) AND
(HISTORY."WOLINKKEY" = 6205 OR
HISTORY."WONO" = 'FY2003F')
ORDER BY
HISTORY."WOLINKKEY" ASC,
HISTORY."WONO" ASC,
HISTORY."HISTKEY" ASC

and again, what is in the select expert:
({?Pm-HISTORY.WOLINKKEY}>1 and
{HISTORY.WOLINKKEY}= {?Pm-HISTORY.WOLINKKEY})
or
({?Pm-HISTORY.WOLINKKEY}=1 and
{HISTORY.WONO}={?Pm-HISTORY.WONO})

synapsevampire · Mar 3, 2003

It appears that everything is being passed, so the performance appears to be on the database side, the server should be returning a subset of data, which is the point of the record selection criteria.

I'd guess that your performance issue is with indexes/statistics.

Copy the above SQL and execute it in a query tool on your database to test this.

-k

synapsevampire · Mar 3, 2003

It appears that everything is being passed to the server, so the performance concern appears to be on the database side, the server is returning a subset of data, which is the point of the record selection criteria.

I'd guess that your performance issue is with indexes/statistics.

Copy the above SQL and execute it in a query tool on your database to test this.

-k

pelajhia · Mar 3, 2003

Thanks you so much for taking the time to review this with me. I will check this out from the db side with one of our dbas.

pelajhia · Mar 3, 2003

Well, I was able to wrangle access to a query design tool, and while messing around with the sql I realized how incorrectly crystal is translating what I have put in the select expert. Like lyanch, I am really scratching my head about how crystal has taken the select expert statement and basically created an incorrect hybrid statement.
It should really have an 'AND' where it has placed the 'OR', and I don't know why I didn't notice before. It's just plain wrong.

I have checked and double-checked all my parentheses, etc. (With the 'AND' in the query design tool, it runs great. With the 'OR', it selects 280000 records, of course.)
I feel like I am beating a dead horse, but I guess I am just curious to have this confirmed. Is my horse dead, or does this ring any bells for anyone, now that the problem is a bit more clear?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

&quot;if then&quot; in select formula??

Programmer

Programmer

Instructor

Instructor

Programmer

Instructor

Programmer

Instructor

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor

"if then" in select formula??