How to send Or clause to SQL

[CRDvlpr]

I have a record selection formula which has a Or clause which is pasted below. Everything except the Line with the OR clause is not send in the SQL. Is there a way to fix this.


(isnull({TCL_SRVC.END_DT}) or {TCL_SRVC.END_DT} >= {?Start Date} ) and

{TCL_SRVC.BGN_DT} <= {?End Date} and

{TRSRC_SERVICE.SRVC_CATEGORY_CDE} = &quot;OOH&quot; and

{TCLIENT.HOME_SCHOOL_DISTRICT} = {?one_tclient_home_school_district} and

{TSRVC_ADDR.ADDR_TYPE} in [&quot;BOTH&quot;, &quot;LOCATION&quot;] and

isnull({TSRVC_ADDR.EFF_END_DT})

 

[synapsevampire]

The statement &quot;everthing except the line with the or clause is NOT send&quot; is surprising.

The use of parentheticals is critical to help assure pass through, as is the version of Crystal, database used and connectivity that you're using, please supply technical information.

Try:

(
isnull({TCL_SRVC.END_DT})
or
{TCL_SRVC.END_DT} >= {?Start Date}
)
and
(
isnull({TSRVC_ADDR.EFF_END_DT}
)
and
(
{TCL_SRVC.BGN_DT} <= {?End Date}
)
and
(
{TRSRC_SERVICE.SRVC_CATEGORY_CDE} = &quot;OOH&quot;
)
and
(
{TCLIENT.HOME_SCHOOL_DISTRICT} =
{?one_tclient_home_school_district}
)
and
(
{TSRVC_ADDR.ADDR_TYPE} in [&quot;BOTH&quot;, &quot;LOCATION&quot;]
)

If this doesn't seem to work, try placing each section in one at a time until you find the area that is failing.

-k

 

[CRDvlpr]

My client has Crystal Version 7. I think this will work in 8 and higher. I was wondering if there is a work around.


Thanks

 

[synapsevampire]

There's nothing here that won't work in 7, but passing SQL can be trickier in 7.

But since you don't have time to answer my questions, nor elaborate on what didn't work in the above when trying one piece at a time, I can't really help you.

I have a FAQ here on passing SQL.

-k

 

[CRDvlpr]

Hi,

I was reading your post and i saw only version number. I didn't see the database information that you asked for. Sorry for that.

Version - Crystal 7
Database - Oracle 8
Connectivity - Oracle driver.

Problem: Part of Record selection formula missing in the Where clause of SQL. The part missing is
(
isnull({TCL_SRVC.END_DT})
OR
{TCL_SRVC.END_DT} &gt;= {?Start Date}
)
. All the other parts of the formula goes fine in the SQL. This is the one that has Or Clause and its missing.

I hope i have elaborated on my problem. Its not that i have no time. Its because i had a deadline yesterday and was focussing on that.

Thanks for your reply.

 

[synapsevampire]

Try:

(
isnull({TCL_SRVC.END_DT})
)
OR
(
{TCL_SRVC.END_DT} &gt;= {?Start Date}
)

I think that you should be using either the CR supplied Oracle driver, or the CR native connectivity.

-k

 

[CRDvlpr]

Hi,

I have already tried that, it doesn't work.

Yeah I'm using the CR supplied Oracle Driver.

Thanks.

 

[synapsevampire]

Try just the isnull, then add in each section and let me know where it fails.

I just tried the same syntax in CR 8.5 and it worked fine.

I think that a bit more discovery is in order, such as what is that data type of this date field?

I have on occasion had to resort to creating a datetime from a parameter using a formula and referencing the formula in the record selection formula to get Crystal to pass the SQL, so try that.

I've never been stumped with passing SQL so don't give up.

-k

 

[CRDvlpr]

I tried what you had asked me to do. This is what i found.

Isnull part is not going in SQL. Anything after Isnull or before Isnull goes in the SQL. Isnull won't go irrespective of using OR or AND.

Thanks again for trying to help.

Thanks

 

[CRDvlpr]

The datatype of the field tcl_srvc.end_dt is date.