Crystal report - issue when using OR condition in Record Selection

403 Views

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