Record Selection not reflected in Crystal SQL Query

[steve053]

Using Crystal XI and MS SQL Server 2005

Here is the record selection criteria

(
 (
  {SURGICAL_HX.COMMENTS} like "*mastec*" and
  {CLARITY_EAP.PROC_CODE} like "990500" and
  not({SURGICAL_HX.COMMENTS} like "*partial mastectomy*")
 )
 or 
 (
  {CLARITY_EAP.PROC_CODE} like 
    ["19180*","19182*","19200*","19220*","19240*",
    "19303*","19304*","19305*","19306*","19307*"]
 )
)

When I look at the SQL Query in Crystal this is what I find

 WHERE  
(
 "SURGICAL_HX"."COMMENTS" LIKE '%mastec%' AND 
 "CLARITY_EAP"."PROC_CODE" LIKE '990500' AND
 "SURGICAL_HX"."COMMENTS" NOT  LIKE '%partial mastectomy%'  
OR 
 (
  "CLARITY_EAP"."PROC_CODE" LIKE '19180%'OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19182%'OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19200%' OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19220%' OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19240%' OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19303%' OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19304%' OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19305%' OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19306%' OR
  "CLARITY_EAP"."PROC_CODE" LIKE '19307%'
 )
)

For whatever reason it will not respect my parens and retunrns incorrect data.

I did get this to work correctly by creating a command, but would like to know if there is a way to force Crystal to honor my groupings in the record selection criteria.

 

[dgillz]

There is your never a guarantee that the record selection formula will translate into SQL, however your report should still return the right records.

Please post some sample data the report returns and indicate which records should not be there.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Madawc]

You could put the two blocks of commands in formula fields. Done without an 'If' or 'Then', you get a boolian, a formula that returns 'True' or 'False'

Just putting the name of the formula field in your selection should do the job. If it doesn't, then do a test report showing the raw data and the formulas, True or False.

If a boolian returns a blank, that means you've hit a null and should do IsNull first.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I don't see anything wrong with the way the SQL is translating--I think it reflects your original selection formula, so the question becomes what you are trying to say here.

-LB