How to construct "Set User Selection" for this?

Andrew2009

Well Known Member
Here are my fields

Car, Brand, Color, Units

Here's my data. I have max of 6 records

1) Honda, Civic, White, 1
2) Honda, Civic, Black, 2
3) Honda, Civic, Silver, 3
4) Honda, Civic, Green, 4
5) Honda, Civic, Blue, 5
6) Honda, Civic, Gray, 6

As you can see, Car and Model fields are the same. The only differences are Color and Units. I'm planning to have 4 "Set User Selection" statements for each record (24 total). I don't know if there's a limit to number of conditions in data selection or not. For example

Car Equal Honda And
Brand Equal Civic And
Color Equal White And
Units Equal 1 OR

Car Equal Honda And
Brand Equal Civic And
Color Equal Black And
Units Equal 2 OR

Car Equal Honda And
Brand Equal Civic And
Color Equal Silver And
Units Equal 3 OR

etc....

The last record will end with None instead of OR since it's the last one.

Basically I want to return all those 6 records from a business view in a UBE using those criteria. Do you have a better way to do it? I have to use Set User Selection in my report because those data were passed in dynamically.


Thanks
 
Last edited:

peterbruce

Legendary Poster
Andrew,

If columns 1 and 2 are the same for all rows in the table/view then there is no need to select on those columns.

I don't know what the maximum number of set selection functions would be, but you could see if there is anything on the Oracle support site or try it and see. My guess is that it would be more than 24.

The next thing is that the "OR" is in the wrong place. Even though, in the set selection function, the "OR"/"AND" is to the right, in the SQL it will be to the left:

Set Selection:
Car Equal Honda And
Brand Equal Civic And
Color Equal Silver And
Units Equal 3 OR

SQL (Note that the first "AND" is not actually used in this sense):
Where Car = Honda
AND Brand = Civic
AND Color = Silver
OR Units = 3

See the attachment to this post: http://www.jdelist.com/vb4/showthread.php/17405-ER-For-Data-Selection?p=59783&viewfull=1#post59783

So your set selection functions (including the first 2 columns) should be:

Car Equal Honda And
Brand Equal Civic And
Color Equal White And
Units Equal 1 AND

Car Equal Honda OR
Brand Equal Civic And
Color Equal Black And
Units Equal 2 AND

Car Equal Honda OR
Brand Equal Civic And
Color Equal Silver And
Units Equal 3 AND

etc....
 
Top