Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Oracle - Issue with WHERE clause

Hello all

I have an issue with a where clause for Oracle 9i.

It is whit this part of the query:
AND TL_RATE.NOTES LIKE '%' || ('00013022497M','00013096747M' || '%'

In the TL_RATE.NOTES, i mac see value that can look like this: '   BOL:00013022497M'

So if i want to search for 00013022497M value or many values at the same time based on the TL_RATE.NOTES, how can i do that?

Thanks again

 WHERE       LOAD.RATE_KEY = TL_RATE.RATE_KEY
         AND TL_RATE.LANE_ID = LANE_RATE_NETWORK.ID
         AND LOAD.BEST_DEP > TL_RATE.EFFECTIVE
         AND LOAD.BEST_DEP < TL_RATE.DATE_INVALID
         AND TL_RATE.LANE_ID = TL_RATE_DETAIL.LANE_ID(+)
         AND TL_RATE.TARIFF_CLASS_ID = TL_RATE_DETAIL.TARIFF_CLASS_ID(+)
         AND TL_RATE.CARRIER_ID = TL_RATE_DETAIL.CARRIER_ID(+)
         AND TL_RATE.EFFECTIVE = TL_RATE_DETAIL.EFFECTIVE(+)
         AND TL_RATE.NOTES LIKE '%' || ('00013022497M','00013096747M' || '%'

Open in new window

Avatar of Qlemo
Qlemo
Flag of Germany image

You will have to use single like expressions, and OR them together:
AND (   TL_RATE.NOTES LIKE '%' || '00013022497M' || '%'
        OR TL_RATE.NOTES LIKE '%' || '00013096747M' || '%'
)

Open in new window

I'm assuming you do not want to have literal values there, else the concatenation is unnecessary of course. Another way is:
... FROM ...
JOIN (select '00013022497M' Note from dual union all select '00013096747M' from dual) notes
ON TL_RATE.NOTES LIKE '%' || notes.Note || '%'
WHERE ... /* without the LIKE condition */

Open in new window

Avatar of Wilder1626

ASKER

Hi again

This work:
AND (   TL_RATE.NOTES LIKE '%' || '00013022497M' || '%'
        OR TL_RATE.NOTES LIKE '%' || '00013096747M' || '%'
)

But the problem is that i will query about 100 or more different values.

Is there a easier way then to create 100 or conditions?
SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello again

Yes, all the values comes from an excel sheet a receive every monday.

Based on the result, i query the table based on these excel values.

I will read your post again and try to understand it and try it.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello all

Sorry for the delay.

Ok, let me take a look at all this and i will all let you know.

Thanks again for all your help
Hi all,

Thanks again to all. Finally, i left the query the way it was.