Multiple Parameter Selects

[Spenello]

Crystal 8.5 with Oracle 10i
All VARCHAR2 fields.
Have four parameters and Record Selection formula in part below:
and ({TABLE.COLUMN1} = {?COLUMN1} or {?COLUMN1} = "")
and ({TABLE.COLUMN2} = {?COLUMN2} or {?COLUMN2} = "")
and (UPPERCASE({TABLE.COLUMN3}) = UPPERCASE({?COLUMN3) or UPPERCASE({?COLUMN3) = "")
and (UPPERCASE({TABLE.COLUMN4}) like '*' + UPPERCASE({?COLUMN4}) + "*" or trim({?COLUMN4}) = "")
Parameters are designed to return all values if no criteria is entered into parameter field.
Problem:
When COLUMN1 and COLUMN4 lines are commented out, report returns 49 rows (verified with SQL in Oracle.
When either or both COLUMN1 and COLUMN4 lines are uncommented out, 6 rows are returned.
Why do more than two parameters return improper amount of rows?

 

[Turkbear]

Hi,
Can you post the SQL code created by CR when this is used ( both with and without the commented out parts)

Could COLUMN1 or COLUMN4 contain NULLS and not just blanks?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Spenello]

Turkbear, I know it's hard to troubleshoot from the hip, but, I would prefer not to post specific code. Yes, there are nulls involved. All columns do have null values.
There are two tables and they are left outer joined. That was the initial problem with two parameters and not all rows were returned. When the tables were left outer joined, all 49 rows returned.
The problem now seems when more than two parameters are used.

 

[Turkbear]

Hi,
Try this restatement of your criteria:

(
If {?COLUMN1} = "" then
true
Else
{TABLE.COLUMN1} = {?COLUMN1}
)

AND

(
If {?COLUMN2} = "" then
true
Else
{TABLE.COLUMN2} = {?COLUMN2}
)

AND

(
If {?COLUMN3} = "" then
true
Else
UPPERCASE({TABLE.COLUMN3}) = UPPERCASE({?COLUMN3)) 
)

AND

(If {?COLUMN4} = "" then
true
Else
 UPPERCASE({TABLE.COLUMN4}) like '*' + UPPERCASE({?COLUMN4}) + "*" 
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I'm guessing that columns 1 and 4 are coming from the table that is left joined to the main table--and if you add selection criteria to them, it effectively undoes the left join. If you are unwilling to show the actual field names, then how about copying the SQL query into notepad and then do a search and replace that would at least help us distinguish the tables from each other and see how they are joined and referenced.

There is a way to maintain left outer joins and make selections on the right hand table by using a command where you add the selection criteria into the From clause instead of the Where clause, but it would be hard to show how to do this without the earlier requested info.

-LB

 

[Spenello]

Turkbear:
The restatement of criteria code seems to do the job.

lbass:
Actually, the sql code ended up being the same for both commented and uncommented. When I ran it in oracle, I had to add one more parameter to make it work.
Can't post the original code, bound by agency guidelines. It would be a breach of security to post specific database schema and table names.

Appreciate both your help. Thanks!