EJB QL 'LIKE' CLAUSE

Hi. I am currently making a search module using EJB QL for the search Query (Oracle Database). I am having a problem on searching with NULL values. It goes this way:

This is the current EJB QL that we use:

select * from TABLE1 t1,TABLE2 t2 WHERE t1.deal_project_id=t2.project_id
AND t2.title LIKE ?1
AND t2.description LIKE ?2
AND t1.contract LIKE ?3
AND t1.deal_type_id LIKE ?4
and t1.network_id=?5

If the user did not specify any string in the textfield we just supply the %% escape character. For example:

select * from TABLE1 t1,TABLE2 t2 WHERE t1.deal_project_id=t2.project_id
AND t2.title LIKE '%%'
AND t2.description LIKE '%%'
AND t1.contract LIKE '%%'
AND t1.deal_type_id LIKE '%%'
and t1.network_id=1

But the problem is, instead of including the columns with NULL in them (Since '%%" means that the user did not specify a criteria in the front end), it ignores it. I know that the solutiom to this is to check if one or more of the criteria is NULL but this will require a lot of EJB finder methods. Does anyone have a good solution to this problem? Your help will be greatly appreciated. Thanks a lot.

Regards!
animorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mayank SAssociate Director - Product EngineeringCommented:
I prefer using LIKE '%%' for all cases.... even in the first case, I would have used LIKE '%1'. Did you try printing the query and see what it is. Run the same query in Oracle and see the result.
Richard QuadlingSenior Software DeveloperCommented:
Can you try ...

SELECT
      *
FROM
      TABLE1 t1,
      TABLE2 t2
WHERE
      t1.deal_project_id = t2.project_id
      AND (t2.title LIKE '%%' OR t2.title IS NULL)
      AND (t2.description LIKE '%%' OR t2.description IS NULL)
      AND (t1.contract LIKE '%%' OR t1.contract IS NULL)
      AND (t1.deal_type_id LIKE '%%' OR t1.deal_type_id IS NULL)
      AND (t1.network_id=1 or t1.network_id IS NULL);


Regards,

Richard.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard QuadlingSenior Software DeveloperCommented:
A better way would be to build a query in code.

e.g.

Start with

SELECT * FROM TABLE1 t1, TABLE2, t2 WHERE t1.deal_project_id = t2.project_id

Then, for each of the search criteria the end user can input something into, check to see if it is not blank and then append the string ...

"AND field LIKE value_user_typed"

or

"AND field = value_user_typed"

depending upon what you want.

This way if no criteria entered, no filtering is done.

Richard.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

animorAuthor Commented:
Thanks for the reply guys. RQuadling's first suggestion is good enough to make it work. Since I will be using EJB and we would like to minimize the number of finder methods, it really helps if we can use only one SQL statement to execute the search.

(awards him the points)

Thank you very much =)
Richard QuadlingSenior Software DeveloperCommented:
Thank you.

And I only stumbled into this area today as an accident, looking for the PHP questions!
animorAuthor Commented:
you're welcome. i just noticed that it wont work if the user provides a criteria and still stumbles upon null values, the query will return even those that have null values. Maybe i should check first the categories supplied as what you suggested in your second post.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.