Help with selection

[jaburke]

I am going crazy. I am helping a co-worker with a crystal report and here's what we're trying to do. Return data where noun_mod_2 <> 'ATTACHMENT 4'. The field noun_mod_2 also contains nulls, so we know we need to evaluate for those because they won't evaluate in the not equal. If I do this in SQL, it works fine. When I add it to the record selection formula, I am getting 0 records. I run the report with no criteria in the record selection and I get 72 records. I know, based on my db query, when I add this, I should get 30 records. So, this is what I add to the record selection:

({Command.NOUN_MOD_2} <> "ATTACHMENT 4" or isnull({Command.NOUN_MOD_2}))

I get 0 records. The corresponding line of SQL when I freehand it, looks like this:

AND (CAT1.NOUN_MOD_2 <> 'ATTACHMENT 4' or CAT1.NOUN_MOD_2 is null)

Any help would be much appreciated.

 

[Turkbear]

Hi,
Crystal needs to have the NULL check/test first in any selection criteria formula, so try:

isnull({Command.NOUN_MOD_2}) 

OR

{Command.NOUN_MOD_2} <> "ATTACHMENT 4"

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[briangriffin]

If the dataset is coming from a command anyway, wouldn't it be preferable to add the working SQL to the command?

 

[lbass]

They absolutely should build this into the command as Brian suggests--otherwise the selection criteria are applied locally, slowing the report.

-LB

 

[Turkbear]

Hi,
Those are great points LB and Brian, I totally missed the fact that it was a Command object not table access.

I guess the Command.NOUN_MOD_2 should have been a clue



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jaburke]

Thanks, I will try it. The reason I dont have it built into the command is because there's a parameter that flags an or statement in the report selection.

((filter = 'Yes' and {Command.NOUN_MOD_2} = "ATTACHMENT 4") or ((filter = 'No' and (isnull({Command.NOUN_MOD_2}) OR
{Command.NOUN_MOD_2} <> "ATTACHMENT 4"))

Will this work, based on your suggestion above or does the isnull have to be the very first thing evaluated in the entire report selection criteria?

Thanks again.

 

[jaburke]

I just tried it and it worked. THANK YOU THANK YOU!! I would have never figured that out. I learned a good lesson today!

 

[lbass]

If "filter" is a parameter, you still should be building everything into the command and creating the parameter {?filter} within the command. Then you would reference it like this:

(
(
'{?filter}' = 'Yes' and
table.NOUN_MOD_2 = 'ATTACHMENT 4'
) or
(
'{?filter}' = 'No' and
(
table.NOUN_MOD_2 is null OR
table.NOUN_MOD_2 <> 'ATTACHMENT 4'
)
)
)

-LB