cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal report - issue when using OR condition in Record Selection

rakeshkumar_bhure
Participant
0 Kudos

Hi Experts,

I am using oracles as database using ODBC connection in CR2011.

I have 7 parameters like below-

1.Date     -mandatory, range

2.Customer     -optional

3.Chain ID     -optional

4.Group     -optional

5.Item     -optional

6.Dept     -optional

7.Class     -optional

When creating record selection using Selection Expert, I am getting record selection formula as below-

{TableQ.Date} = {?Date} and

(not HasValue({?Customer}) OR {TableQ.Customer} = {?Customer}) and

(not HasValue({?Chain ID}) OR {TableQT.Chain ID} = {?Chain ID}) and

(not HasValue({?Group}) OR {TableQ.Group} = {?Group}) and

(not HasValue({?Item}) OR {TableQ.Item} = {?Item}) and

(not HasValue({?Dept}) OR {TableQ.Dept} = {?Dept}) and

(not HasValue({?Class}) OR {TableQ.Class} = {?Class})

I have observed that when we provide values to parameter, based on it, CR is generating a SQL Query and passing it to database to get data. I can see respective fields in WHERE clause of SQL Query, if I give values to them in prompt.

But According to my requirement, I have changed the record selection formula as below-

{TableQ.Date} = {?Date}

and

(

(not HasValue({?Customer}) OR {TableQ.Customer} = {?Customer}) or

(not HasValue({?Chain ID}) OR {TableQT.Chain ID} = {?Chain ID}) or

(not HasValue({?Group}) OR {TableQ.Group} = {?Group})

)

and

(

(not HasValue({?Item}) OR {TableQ.Item} = {?Item}) or

(not HasValue({?Dept}) OR {TableQ.Dept} = {?Dept}) or

(not HasValue({?Class}) OR {TableQ.Class} = {?Class})

)

Now when I am giving values to parameters and viewing SQL Statement, I can see only DATE in WHERE clause. Others are not appearing,if I provided values to them also. It is taking too much of time to get data if we have date only in WHERE clause.

Can any body told me why it is behaving like this and how to rectify it.

Thanks in advance.

Regards

Rakesh

Accepted Solutions (0)

Answers (2)

Answers (2)

abhilash_kumar
Active Contributor
0 Kudos

Hi Rakesh,

Shouldn't the Record Selection Formula look like this:

{TableQ.Date} = {?Date}

AND

(

     (not HasValue({?Customer}) OR {TableQ.Customer} = {?Customer}) and

     (not HasValue({?Chain ID}) OR {TableQT.Chain ID} = {?Chain ID}) and

     (not HasValue({?Group}) OR {TableQ.Group} = {?Group}) and

     (not HasValue({?Item}) OR {TableQ.Item} = {?Item}) and

     (not HasValue({?Dept}) OR {TableQ.Dept} = {?Dept}) and

     (not HasValue({?Class}) OR {TableQ.Class} = {?Class})

)

-Abhilash

rakeshkumar_bhure
Participant
0 Kudos

Hi Abhilash,

I don't know, but CR is generating by itself what I mentioned earlier.

But When I edit it for OR condition, Only date is coming in WHERE Clause of SQL generated by CR. SO It is fetching data from Database based on Date (which is slow) and once it is filtering it locally based on other parameter values. I need that CR should fetch data based on other parameter values(if available) when we have OR condition in Record Selection.

Thanks

Former Member
0 Kudos

Hi Rakesh,

Can you test this from SQL editor directly on the database.

-Prathamesh

rakeshkumar_bhure
Participant
0 Kudos

HI,

In Database also, when we have date only in where clause it is taking time. So User can give value for group and/or Dept or other parameters. And CR should get data from database for these selection. Right now it it taking data based on date only from database and locally filtering this data based on other prompt value provided. This is the case when we have OR condition in Record Selection.