Crystal 8.5 - SQL generated by 'Select Expert' loses logical meaning

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Oli Ollerenshaw (mroshaw_at_ukonline.co.uk)
Date: 07/16/04 

Date: 16 Jul 2004 00:08:34 -0700

Hello,

I have created the following formula in Crystal 8.5:

({S_SRV_REQ.X_QA_HELD_DATE} in {?Start Date} to {?End Date})
Or
(ISNULL({S_SRV_REQ.X_QA_HELD_DATE}) And
ISNULL({S_SRV_REQ.X_HELD_FLAG}))

i.e. this should return True if EITHER of the bracket enclosed AND
statements are True.

This is translating to the following ODBC (Oracle) SQL:

(S_SRV_REQ."X_QA_HELD_DATE" >= {ts '2004-07-14 00:00:00.00'} AND
S_SRV_REQ."X_QA_HELD_DATE" < {ts '2004-07-15 00:00:00.00'} OR
S_SRV_REQ."X_QA_HELD_DATE" IS NULL AND
S_SRV_REQ."X_HELD_FLAG" IS NULL)

As you can see, Crystal is stripping out the brackets surrounding the
two separate AND clauses. The correct SQL should be:

((S_SRV_REQ."X_QA_HELD_DATE" >= {ts '2004-07-14 00:00:00.00'} AND
S_SRV_REQ."X_QA_HELD_DATE" < {ts '2004-07-15 00:00:00.00'}) OR
(S_SRV_REQ."X_QA_HELD_DATE" IS NULL AND
S_SRV_REQ."X_HELD_FLAG" IS NULL))

The two SQL statements have different logical meanings and the results
of the report are incorrect:

should be:

(x AND y) OR (a AND b)

Crystal is evaluating this as:

x AND y OR a AND b = ((x AND y) OR a) AND b

Can anyone tell me how to force Crystal to translate this formula
correctly, without having to resort to manually updating the SQL
(which works, incidentally) and thereby losing the benefits of the
Select Expert?

Cheers!

mroshaw

Relevant Pages

  • Re: Critique of Robert C. Martins "Agile Principles, Patterns, and Practices"
    ... code in your book is WASTED on translating back and forth between two ... To the right the code deals with SQL, tables, rows, ... Martin commits a common OO mistake of making a hierarchy of "employee ...
    (comp.object)
  • Re: Prolog to SQL ..??
    ... >> There are lots of nice tricks, but translating Prolog to SQL is not ... >> There are obviously times when a Prolog clause or 2 can be translated ... >> If you want something REALLY tedious, try Prolog to Yacc. ...
    (comp.lang.prolog)
  • Re: Prolog to SQL ..??
    ... > There are lots of nice tricks, but translating Prolog to SQL is not ... > so strange if you just treat SQL like an assembly language;-) and use ... > There are obviously times when a Prolog clause or 2 can be translated ... The problem with Yacc is that he make only finite state automata, ...
    (comp.lang.prolog)
  • Re: Prolog to SQL ..??
    ... There are lots of nice tricks, but translating Prolog to SQL is not ... so strange if you just treat SQL like an assembly language;-) and use ... There are obviously times when a Prolog clause or 2 can be translated ...
    (comp.lang.prolog)
  • Re: DBD::mysql problem
    ... >> The problem is that when the SQL, which just gets a one column one row ... > However, if returning no rows is not an error, you should not die based ... The main thing is it was evaluating the return value of 0 as ... or the undef of an empty row. ...
    (comp.lang.perl.modules)