• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

Interpretation of boolean operators in Record Selection

Hi,

I have the following code in my record selection formula:

   ({CONTACT2.UDELIVERY1} >= {?start_date} AND {CONTACT2.UDELIVERY1} <=  {?end_date})
OR ({CONTACT2.UDELIVERY2} >= {?start_date} AND {CONTACT2.UDELIVERY2} <=  {?end_date})
OR ({CONTACT2.UDELIVERY3} >= {?start_date} AND {CONTACT2.UDELIVERY3} <=  {?end_date})
OR ({CONTACT2.UDELIVERY4} >= {?start_date} AND {CONTACT2.UDELIVERY4} <=  {?end_date})
OR ({CONTACT2.UDELIVERY5} >= {?start_date} AND {CONTACT2.UDELIVERY5} <=  {?end_date})
OR ({CONTACT2.UDELIVERY6} >= {?start_date} AND {CONTACT2.UDELIVERY6} <=  {?end_date})

When Crystal converts this into the SQL where clause it comes out like this:

 (CONTACT2."UDELIVERY1" >= {ts '2003-09-15 00:00:00.00'} AND
    CONTACT2."UDELIVERY1" < {ts '2003-10-18 00:00:00.00'} OR
    CONTACT2."UDELIVERY2" >= {ts '2003-09-15 00:00:00.00'} AND
    CONTACT2."UDELIVERY2" < {ts '2003-10-18 00:00:00.00'} OR
    CONTACT2."UDELIVERY3" >= {ts '2003-09-15 00:00:00.00'} AND
    CONTACT2."UDELIVERY3" < {ts '2003-10-18 00:00:00.00'} OR
    CONTACT2."UDELIVERY4" >= {ts '2003-09-15 00:00:00.00'} AND
    CONTACT2."UDELIVERY4" < {ts '2003-10-18 00:00:00.00'} OR
    CONTACT2."UDELIVERY5" >= {ts '2003-09-15 00:00:00.00'} AND
    CONTACT2."UDELIVERY5" < {ts '2003-10-18 00:00:00.00'} OR
    CONTACT2."UDELIVERY6" >= {ts '2003-09-15 00:00:00.00'} AND
    CONTACT2."UDELIVERY6" < {ts '2003-10-18 00:00:00.00'})

Note the loss of the brackets which totally changes the results of the query...   crystal has changed my logic from (... AND ...) OR (... AND ....) OR (... AND ...) to being like this (... AND ... OR ... AND .... OR ... AND ...).  Is this what crystal is supposed to do?  If so, how do I get it to behave like I want?

I have tried putting extra sets of brackets in, ie, ((... AND ...)) OR ((... AND ...)) OR ((... AND ...)), I have also tried using the 'In' clause instead, but that produces the same incorrect SQL.

I am aware that I could use a stored procedure instead (which I have done successfully) but I cannot install this at my clients site because the report is run from a 3rd party product which does not support Crystal Reports that are generated from storted procedures....

Thanks in advance.

0
_option_
Asked:
_option_
  • 12
  • 10
  • 2
1 Solution
 
mlmccCommented:
Have you tried the report with the SQL it changed to?  Were the results as expected?

I believe AND is higher precedence than OR therefore the ( ) aren't needed.  The ANDs will be done first which is what your ( ) implied then the ORs.

mlmcc
0
 
_option_Author Commented:
mlmcc,  you are correct - the brackets I was using are superfluous.  Running the SQL that Crystal generates in Query Analyzer returns the correct results....

However the report does not display those results.  It only shows the records that match the first criteria set (ie, CONTACT2."UDELIVERY1" >= {ts '2003-09-15 00:00:00.00'} AND CONTACT2."UDELIVERY1" < {ts '2003-10-18 00:00:00.00'} in the example above).

I have recreated the report to ensure that there is nothing else intefering with the results (its a VERY simple report, only the one table, an id for the contact in the details section, 2 parameter fields for the start and end date, there is no grouping or anything else...)

I just assumed the loss of the brackets was causing the problem...  I am more confused now than when I thought the SQL was corrupt.

.........

OK, I have just tried setting the same scenario up with different table/database/datasource etc, and the same thing is happening.  I got 3 date fields from a table and applied a date range for all 3 (as per above but using only 3 fields instead of 6) - SQL code that crystal generates displays correct results but crystal only shows those matching the first AND.  Am I missing something obvious again?  WTF??


0
 
mlmccCommented:
What database are you using?

Are you certain there is data for the other delivery dates?

mlmcc
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
_option_Author Commented:

I am using SQL Server 2000 (sp3) and Crystal 8.5.

I am totally sure that the data is not the problem.  If I copy and paste the sql generated by crystal into query analyzer, it returns the results I expect.
0
 
mlmccCommented:
I have some reports with very complex where clauses but they use different fields.  I will have to test this to see if I can create the same problem?

mlmcc
0
 
_option_Author Commented:
That would be great.  I'm happy to throw in some more points for you ...

I find this a bit bizarre.  I too have created reports with much more complexity than this... and they have worked fine.  It seems like something that would be done too often to exist as a bug and not be widely known about....
0
 
mlmccCommented:
JUst thought of something.  I don't have sufficient research cpablity here at home but does MS SQL limit the significant characters in a name to say 8 or 9?

Can you modify the table to say change one the UDELIVERYx to some other name (say UDELIVERY2 to ARRIVED) then fix you rcode appropriately?

See if that helps.

mlmcc

0
 
_option_Author Commented:
mlmcc, excellent thought, however...

I created another report (based on a different table in different database - still SQL Server though...) using three date fields called callback_dt, respond_dt and closed_dt.  I had the same problem... the query from crystal returns correct results in Analyzer but the report only show the records that meet the criteria for the callback_dt fields restriction.  The SQL crystal generated was as follows:

SELECT
    sv_inquiry."callback_dt", sv_inquiry."respond_dt", sv_inquiry."closed_dt", sv_inquiry."account_id"
FROM
    "Integra"."dbo"."sv_inquiry" sv_inquiry
WHERE
    (sv_inquiry."callback_dt" > {ts '1999-11-08 23:59:59.00'} AND
    sv_inquiry."callback_dt" < {ts '1999-11-15 00:00:00.00'} OR
    sv_inquiry."closed_dt" > {ts '1999-11-08 23:59:59.00'} AND
    sv_inquiry."closed_dt" < {ts '1999-11-15 00:00:00.00'} OR
    sv_inquiry."respond_dt" > {ts '1999-11-08 23:59:59.00'} AND
    sv_inquiry."respond_dt" < {ts '1999-11-15 00:00:00.00'})

but crystal only showed results as if the query read:

SELECT
    sv_inquiry."callback_dt", sv_inquiry."respond_dt", sv_inquiry."closed_dt", sv_inquiry."account_id"
FROM
    "Integra"."dbo"."sv_inquiry" sv_inquiry
WHERE
    sv_inquiry."callback_dt" > {ts '1999-11-08 23:59:59.00'} AND
    sv_inquiry."callback_dt" < {ts '1999-11-15 00:00:00.00'}
0
 
mlmccCommented:
Just throwing mud to see if anything sticks.

mlmcc
0
 
mlmccCommented:
What is ts?
{ts '2003-09-15 00:00:00.00'}

When I build my selection through the select expert I get

"Sep 15 2003 12:00:00AM"

mlmcc
0
 
_option_Author Commented:
....Sorry for the delay.  Just had to change continents.

I think ts is short for timestamp...  its basically a SQL Server/Access compatible way of inputting a datetime string.

Not sure why it generates differently to yours... maybe regional settings or different versions of SQL Server/Crystal.  Or perhaps there is a setting in crystal that determines how it passes datetime strings?
0
 
mlmccCommented:
I'll keep looking.

mlmcc
0
 
_option_Author Commented:
I'm adding some more points to this because I think anyone trying to answer it will need to recreate my scenario...  my original question was easy, but now it is more complex.
0
 
mlmccCommented:
I just built one like yours for one of my reports with similar results.  I could get it to take the first and second field but not a third.  No idea what the problem is.

I'll try looking at the Crystal site.

mlmcc
0
 
_option_Author Commented:
Thanks.  Good to know I am not the only one with this problem.  
0
 
_option_Author Commented:
I found an issue similar to this on the Crystal website, and they claimed it was fixed with the latest service pack (which is installed on my pc).  I think I am going to have to find another way of doing this, I already have a version of the report that works based on a stored proc - was hoping to not have to use it but I think its the only solution.

Have you had any success?

Is anyone else out there that can help?
0
 
mlmccCommented:
No luck as yet.

mlmcc
0
 
mlmccCommented:
I wonder if you are running into a length problem

Where are you building the SQL?

Can you build it like

SELECT
callback_dt, respond_dt, closed_dt, account_id
FROM
sv_inquiry
WHERE
(callback_dt > {ts '1999-11-08 23:59:59.00'} AND
callback_dt < {ts '1999-11-15 00:00:00.00'} OR
closed_dt > {ts '1999-11-08 23:59:59.00'} AND
closed_dt < {ts '1999-11-15 00:00:00.00'} OR
respond_dt > {ts '1999-11-08 23:59:59.00'} AND
respond_d" < {ts '1999-11-15 00:00:00.00'})

mlmcc
0
 
_option_Author Commented:
Crystal is building the SQL, based off my entry in the record selection formula.  I have tried constructing the RSF in several ways but they all produce SQL like the above.
0
 
mlmccCommented:
I ran across this tonight when searching for another answer.  It seems related though it claims the problem was solved in CR8.

http://support.crystaldecisions.com/library/kbase/articles/c2003246.asp

Was it reintroduced in CR8.5?

mlmcc
0
 
_option_Author Commented:
Interesting....

This problem seems to suggest that the issue is with the record selection formulae not properly translating to SQL code... in my scenario the SQL is perfect but the displayed results are different to what the SQL returns.  Unless, of course, the SQL displayed in the Show SQL Query window is different to what is actually sent to the database....

MJC
0
 
mlmccCommented:
If I read the Crystal article correctly that is exactly what was happening.  When an OR was included the record selection formula wasn't sent or only part of it was.

mlmcc
0
 
Jules_MadgwickCommented:
Has anybody had any more luck on this.
I have the same problem.
My SQL query gets cut down.  Only taking hte last part of the query ???
0
 
Jules_MadgwickCommented:
One thing I have noted is that the SelectionFormula should only be set once.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now