Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

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

0
Wilder1626
Asked:
Wilder1626
4 Solutions
 
QlemoC++ DeveloperCommented:
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

0
 
Wilder1626Author Commented:
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?
0
 
flow01Commented:
In Oracle 9i the possibilities are limited. (also by maximum length of 1 single sql-statement)

As a basic: use the "other" way of Qlemo (= providing the search arguments as a table).

Do you have possibility to create a new table ?

Where will the search values come from ? Are they already available in another table, in a word-document, in a excel-file ?

For example  in excel and values in a single colomn  A
make a formula like
= "select '"& A1 & "' note from dual union"
to get your ORACLE clause
or
= "insert into arguments (argument) values ('"& A1 & "');
to get insert statement in the new arguments table
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Wilder1626Author Commented:
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.
0
 
QlemoC++ DeveloperCommented:
With some effort it is possible to read the Excel sheet directly, treating it as a table in Oracle. There is
(a) Heterogenous Services to access DBs of different vendors; or
(b) external tables, which would require you export the Excel into e.g. CSV (see http://www.oracle-developer.net/display.php?id=204 for an example of external tables); or
(c) Loader to import file contents into tables.

Some (GUI) tools support copy & paste of Excel cells into a datagrid representing an Oracle table.
0
 
PortletPaulCommented:
Not exactly sure how your tables join, particularly the table: LANE_RATE_NETWORK
from load
inner join LANE_RATE_NETWORK ON ?????
inner join TL_RATE ON 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.NOTES LIKE '%' || '00013022497M' || '%'
                      OR
                        TL_RATE.NOTES LIKE '%' || '00013096747M' || '%'
                      OR
                        ... /* 100 or so more of these - Yikes! */
                      )
                      
left outer join TL_RATE_DETAIL ON 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.LANE_ID = TL_RATE_DETAIL.LANE_ID

Open in new window


Is  TL_RATE.NOTES the only place where those strings are referenced?
0
 
awking00Commented:
If you create a table (call it search) with the values to be searched for (in a column called value, e.g.), you can then modify your query replacing this -
AND TL_RATE.NOTES LIKE '%' || ('00013022497M','00013096747M' || '%'
with this -
AND EXISTS( SELECT 1 FROM SEARCH WHERE INSTR(TL_RATE.NOTES,SEARCH.VALUE) > 0)
0
 
Wilder1626Author Commented:
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
0
 
Wilder1626Author Commented:
Hi all,

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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now