CODEWITHSUNDEEP
oracle11gfiltering
Ree
Ree

Reputation: 903

How to filter many columns together in oracle sql

I need to fetch 5 columns of data from a table in my oracle database. Before the query is executed, the user has the option of filtering (I think is the correct word) the columns. So the user can say he's not fussy and return all entries or can say only where a column contains a specific entry.

The thing I'm struggling with is that I have 5 columns and this is the case for each column. And if a user chooses something specific for all 5 columns then the result returned must have only entries which contain that specific thing for all 5 columns i.e. 'and' between all the search criteria.

I hope I've explained that sufficiently.

Here's what I have so far and doesn't work completely:

select column1, column2, column3, column4, column5 from the_table where

(not exists (select * from the_table where column1=$P{COLUMN1}) or column1=$P{COLUMN1}) AND

(not exists (select * from the_table where column2=$P{COLUMN2}) or column2=$P{COLUMN2}) AND

(not exists (select * from the_table where column3=$P{COLUMN3}) or column3=$P{COLUMN3}) AND

(not exists (select * from the_table where column4=$P{COLUMN4}) or column4=$P{COLUMN4}) AND

(not exists (select * from the_table where column5=$P{COLUMN5}) or column5=$P{COLUMN5})

ORDER BY column4 DESC

The parameters $P{} are user input in a jasper report.

Upvotes: 0

Views: 152

Answers (2)

Ree
Ree

Reputation: 903

SELECT column1, column2, column3, column4, column5 from the_table
WHERE (column1 like CONCAT ($P{column1},'%'))
AND (column2 like CONCAT ($P{column2},'%'))
AND (column3 like CONCAT ($P{column3},'%'))
AND (column4 like CONCAT ($P{column4},'%'))
AND (column5 like CONCAT ($P{column5},'%'))
ORDER BY column4 DESC

this did the trick :)

Upvotes: 1

bob dylan
bob dylan

Reputation: 1498

Binding Parameters to Oracle Dynamic SQL

https://chat.stackoverflow.com/rooms/12464/discussion-between-batuta-and-ollie

What you want is to create a package like the above two links. This should allow you to specify any number of parameters and build the query dynamically.

Upvotes: 0

Related Questions