Edit Selection Formula - has 2 parts, only 1 happens

[ainkca]

Hi all,

I am fairly new to Crystal, and I have some reports to write that have to be done in Crystal 6.0. (I've really only used Crytal 8.5)

My selection formula is this:
{STHISTORY.STHISTORYKEY} > "22000900" and
{STHISTORY.STATUS} in ["D","A"]

When I run it looking at just they key (for testing only) it runs perfectly. When I run it using just the status, that works too (although with millions of records I don't let it run for long).

When I use the above formula, only the status part is followed and it's ignoring the status history key entirely and pulling ALL D's and A's.

Can anyone tell me what I've done wrong here?

Thanks in advance.

 

[Naith]

Can you verify what the SQL being passed looks like? (In this instance, it should contain both clauses.)

Also, is the behaviour consistent regardless of which clause comes first in the selection criteria, and also if:

{STHISTORY.STATUS} in ["D","A"]

is substituted with:

({STHISTORY.STATUS} = "D" or {STHISTORY.STATUS} = "A")

Naith

 

[dgillz]

Also check for nulls in your data. If either field has nulls the behavior can be erratic and you must test for nulls in your formula as well.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[ainkca]

Yes, the SQL looks exactly like that, and it doesn't seem to matter which clause comes first, when they're both there I get everything. I'm only using one table in the report, so it's not a linking issue.)

Here's the SQL exactly:

SELECT
STHISTORY."STHISTORYKEY",STHISTORY."STATUS"
FROM
"PWLDBA"."STHISTORY" STHISTORY
WHERE
STHISTORY."STHISTORYKEY" > '29000900' AND
(STHISTORY."STATUS" = 'A' OR
STHISTORY."STATUS" = 'D')

Looks simple enough, I don't see what could be wrong. I did try it using the IN A or D, and tried having it do that first, I got the same results both times.

 

[ainkca]

Tried adding the NOT ISNULL({STHISTORY>STATUS}) like this:

{STHISTORY.STHISTORYKEY} > "22000900" AND
NOT ISNULL({STHISTORY>STATUS}) AND
({STHISTORY.STATUS} = "D" OR
[STHISTORY.STATUS = "A")

I get the exact same thing... all the A's and D's and it ignores the STHISTORYKEY.

 

[Naith]

Show me an example of a row coming back that you consider to be outside the remit of the SQL.

Because I note that STHISTORY."STHISTORYKEY" seems to be a varchar field of some kind, containing numerical information; so consider that numeric and string ordering is not the same.

e.g. The string "300" is considered greater than "29000900", whilst the number 300 is treated as being less than 29000900.

Naith

 

[ainkca]

OF COURSE! That makes perfect sense. It's got a '350082' as one of the keys it pulls.

DUH! I should have got that on my own. My first thought was a data type issue, but I dismissed it because it worked when that was the only criteria.

I'm thinking I should format the STHISTORYKEY field to numeric. WOuld I do that with the ToNumber function?

 

[Naith]

Bingo.

 

[ainkca]

Thanks for the help. It's taking a long time to run, but I think that's because there are millions of records in the table. THANKS!

 

[GJParker]

The ToNumber function is a crystal function so the ToNumber({STHISTORY.STHISTORYKEY}) > 22000900 part of the record selection formula will processed on the local PC and not passed to the database, this will be why your report is running slowly.

You could try CDbl({STHISTORY.STHISTORYKEY}) > 22000900 instead this should be passed to the database. You can check this by viewing the SQL generated.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Naith]

Good call, Gary. Guess I wasn't that switched on yesterday.

 

[ainkca]

Thank you very much, I will try that. I was only working a short time yesterday, sorry I didn't see this until now.
THanks again!