Parentheses missing in SQL statement

[RenaG]

I am using Crystal 10.

I have the following select statement (written in the Record Selection Formula Editor). The part I want to focus on is in red:

{Cpt.Template} = 0 and
((isnull({Schedule.location}) or {Schedule.location} = 0  ) or not ({Schedule.location} in [1975,8224])) and
((isnull({Ident.Ident_ID}) or {Ident.Ident_ID}=0 ) or {IDENT.Version}=0) and
{Schedule.app_dttm}={?Appointment Date} and
{Schedule.Version} = 0 and
not ({Schedule.Activity} in ["SWMTG", "99917", "99919", "99920", "99921"]) and
[red](({Schedule.Create_DtTm} >= {?Create Date}) or
({Schedule.Edit_DtTm} >= {?Create Date} and 
{Schedule.Create_DtTm} <  {Schedule.Edit_DtTm})) [/red]and
{Schedule.Inst_ID} = {?Other MSTI Sites}

The SQL statement looks like this (I am only showing the last part with the DtTm fields because it is a complex SQL and would be way more information than needed). I added line breaks to make it easier to read:

("Schedule"."Create_DtTm">={ts '2012-04-27 00:00:00'} OR
"Schedule"."Edit_DtTm">={ts '2012-04-27 00:00:00'} AND
"Schedule"."Create_DtTm"<"Schedule"."Edit_DtTm") AND 
"Schedule"."Inst_ID"=4

My parentheses have been removed and so the select stmt doesn't work correctly. How do I force Crystal to keep the parameters the way I set them?

TIA!
~RLG

 

[Madawc]

Break up the complex Select into a number of separate formula fields. Group fields within fields to get what you're after.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[RenaG]

Hi Madawc,

I like that idea but don't know how to go about it. I tried creating a formula called SelectionDates that looks like this:

If (({Schedule.Create_DtTm} >= {?Create Date}) or
({Schedule.Edit_DtTm} >= {?Create Date} and 
{Schedule.Create_DtTm} <  {Schedule.Edit_DtTm})) then
    true
Else
    false;

And modified the Record Selection Formula to look like this:

{Cpt.Template} = 0 and
((isnull({Schedule.location}) or {Schedule.location} = 0  ) or not ({Schedule.location} in [1975,8224])) and
((isnull({Ident.Ident_ID}) or {Ident.Ident_ID}=0 ) or {IDENT.Version}=0) and
{Schedule.app_dttm}={?Appointment Date} and
{Schedule.Version} = 0 and
not ({Schedule.Activity} in ["SWMTG", "99917", "99919", "99920", "99921", "99924"]) and
{@SelectionDates} and
{Schedule.Inst_ID} = {?Other MSTI Sites}

When I look at the SQL statement, it looks the same.

("Schedule"."Create_DtTm">={ts '2012-04-27 00:00:00'} OR "Schedule"."Edit_DtTm">={ts '2012-04-27 00:00:00'} AND "Schedule"."Create_DtTm"<"Schedule"."Edit_DtTm") AND "Schedule"."Inst_ID"=4

What have I done wrong?

TIA!
~RLG

 

[lbass]

Please explain in what way the results are incorrect.

-LB

 

[Madawc]

You need to break them up into Boolians - tests without an IF, which return True or False. Thus

{Schedule.Create_DtTm} >= {?Create Date}

{Schedule.Edit_DtTm} >= {?Create Date} 
and {Schedule.Create_DtTm} <  {Schedule.Edit_DtTm}

@RecentCreate or @WithinPeriod

Display and check you get the expected answers. (When a Boolian shows blank, this means it has hit a null.)

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[RenaG]

Hi LB~
My user generates a report of appointments scheduled 3 days in the future. For example, the report run today would be for appointments on May 14. Then on May 14 (or the night before) she wants to run another report that will show any new appointments or appointments that were moved to the 14th since she last ran the report.

?Create Date = date 1st report was run

So my logic was:
To get any new appointments
schedule.create_DtTm >= ?Create Date

OR
to get any appoinments that were moved
schedule.Edit_DtTm >= ?Create Date AND
schedule.Create_DtTm < schedule.Edit_DtTm

I don't think I am going to be able to get this to work. Not so much because I can't get the Or and AND to group correctly but because a record could be edited for all kinds of reasons not necessarily for moving an appointment. If you see something that might work, I would be happy to hear what it is.

TIA!
~RLG

 

[RenaG]

Hi Madawc,

I added two formulas and placed them in the Record Selection Formula as you suggested. When I look at the SQL it still doesn't have the parens the way I need them. But I think my logic is wonky anyway so I need to work through that before I continue battling with the parens. Thanks for all your help.

~RLG