Solved

Interpretation of boolean operators in Record Selection

Posted on 2003-10-26
26
354 Views
Last Modified: 2008-01-09
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
Comment
Question by:_option_
  • 12
  • 10
  • 2
26 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 9626977
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
 

Author Comment

by:_option_
ID: 9630580
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9630940
What database are you using?

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

mlmcc
0
 

Author Comment

by:_option_
ID: 9630953

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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9630995
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
 

Author Comment

by:_option_
ID: 9631032
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9631060
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
 

Author Comment

by:_option_
ID: 9631106
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9631347
Just throwing mud to see if anything sticks.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9634724
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
 

Author Comment

by:_option_
ID: 9647361
....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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9654656
I'll keep looking.

mlmcc
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:_option_
ID: 9656631
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9658516
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
 

Author Comment

by:_option_
ID: 9668036
Thanks.  Good to know I am not the only one with this problem.  
0
 

Author Comment

by:_option_
ID: 9729838
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9744559
No luck as yet.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9750369
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
 

Author Comment

by:_option_
ID: 9753237
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 9822190
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
 

Author Comment

by:_option_
ID: 9822495
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 9822554
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
 

Expert Comment

by:Jules_Madgwick
ID: 9988398
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
 

Expert Comment

by:Jules_Madgwick
ID: 9988661
One thing I have noted is that the SelectionFormula should only be set once.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now