CR changes my Selection Criteria in the SQL

[cully651]

Hi! I'm using Oracle 9i and Crystal Reports XI R2 Sp1. Here's my dilema;

I put in a Selection Criteria like this

{Documents.Document_Date} 
in DateTime({?beginDate}) to DateTime({?endDate})
and
({Documents.Author} <> "Jane" and
{Documents.Document_Type} <> "PDF")

Which should mean that any documents in that date range are included unless the Author is Jane AND the document type is PDF. Instead, it changes the SQL to this;

SELECT "Documents"."Document_Num", "Documents"."Author", 
   "Documents"."Document_Date", "Documents"."Document_Type",
   "People"."Name" 
FROM   "Owner"."Documents" "Documents" LEFT OUTER JOIN 
       "Owner"."People" "People" ON     "Documents"."Author"="People"."Name" 

WHERE  ("Documents"."Document_Date">=TO_DATE ('01-01-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
 AND "Documents"."Document_Date"<TO_DATE ('01-01-2007 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
 AND "Documents"."Author"<>"Jane" 
 AND "Documents"."Document_Type"<>"PDF"

Which simply means the to include all of them in the date range, exclude all of them where the Author is "Jane" and exclude all of them where the Document Type is "PDF".

I can't believe CR is ignoring my parens! Is there anything I can do besides writing a special view in Oracle?

 

[Madawc]

Shouldn't your command be

({Documents.Author} <> "Jane" [b]or[/b]
{Documents.Document_Type} <> "PDF")

I'd also suggest putting the command in a formula field of its own - what Crystal calls a Boolian. It will return True or False and you can check it against unselected records before adding it to the selection criteria.

If the boolian were called @Jane_PDF, they you'd just say and @Jane_PDF to invoke it in a selection etc.

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

 

[Madawc]

That last sentence should be:

If the boolian were called @Jane_PDF, they you'd just say and @Jane_PDF to invoke it in a selection etc.

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

 

[cully651]

Thanks for the replies. I tried the formula approach yesterday and it didn't help. I stumbled on the answer today...

{Documents.Document_Date}
in DateTime({?beginDate}) to DateTime({?endDate})
and NOT
({Documents.Author} = "Jane" and
{Documents.Document_Type} = "PDF")

A tiny change to end 10 hours of frustration!

Thanks very much for your input!