I Are Not Reel ProgramrrowynNovember 14 2008, 00:24:03 UTC
I shouldn't've used Foo for my example, because funfield isn't actually a string field -- it's numeric. With some (but not all!) string fields that are null in the database, using something like String ="" will work.
But with this numeric field, if I look at the null fields in the actual database, they display as blank, not zero. However, in Crystal's report they show as 0:
Re: I Are Not Reel ProgramrterryclothNovember 14 2008, 00:35:35 UTC
Right -- a blank cell is different than a cell with value 0 or value '0 length string'. The underlying database engine probably has special blank-handling formulas, maybe Crystal Reports does too?
Re: I Are Not Reel ProgramrrowynNovember 14 2008, 00:40:12 UTC
Crystal has "isnull" but isnull({funfield}) doesn't get any results either.
Crystal also has a toggle for "force NULL values to 0", I think. I can try playing with that and isnull and =0 in various configurations, and see if I get anything.
Whooa, time snap! Would you believe that one of my very first jobs was writing and formatting Crystal Reports, in 1995 or so? My mom got me the job, since I'd never seen it before then -- rather naked nepotism. Anyway, I can't help you, but I do sympathize.
If you can use raw SQL to generate the report (perhaps by editing the SQL generated by Cyrstal before using it), you could probably fix the incorrect handling of the NULL value. Is sounds like what you want (in ANSI SQL) is:
Select ... From ... Where (Funfield is NULL) or (FunField not in (70, 80, 90))
and Crystal is mangling the interpretation of that expression.
I've never used Crystal Reports, but if there's a way to see what SQL it is generating to retrieve the data, you can better understand the nature of the bug.
Comments 12
Reply
{funfield} not in [70,80,90]
I'm not sure offand if Crystal has another construction for "not" but I'll take a look when I'm at work tomorrow. Can't hurt.
Reply
Reply
But with this numeric field, if I look at the null fields in the actual database, they display as blank, not zero. However, in Crystal's report they show as 0:
Name Funfield
Boo 10
Goo 2
Joo 5
Noo 0
Funfield = 2 would get me:
Name Funfield
Goo 2
But Funfield = 0 just gives:
Name Funfield
No results. >.
Reply
Reply
Crystal also has a toggle for "force NULL values to 0", I think. I can try playing with that and isnull and =0 in various configurations, and see if I get anything.
Reply
Reply
Reply
Select ... From ...
Where (Funfield is NULL) or (FunField not in (70, 80, 90))
and Crystal is mangling the interpretation of that expression.
I've never used Crystal Reports, but if there's a way to see what SQL it is generating to retrieve the data, you can better understand the nature of the bug.
Reply
Leave a comment