Solved

Oracle - Issue with WHERE clause

Posted on 2013-02-02
9
470 Views
Last Modified: 2013-03-02
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
Comment
Question by:Wilder1626
9 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 38847176
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38847243
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
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 125 total points
ID: 38847435
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:Wilder1626
ID: 38847524
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
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 125 total points
ID: 38847609
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 38848025
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
 
LVL 32

Accepted Solution

by:
awking00 earned 125 total points
ID: 38851997
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38854421
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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38946328
Hi all,

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 27
SYbase 4 24
Need help how to find where my error is in UFD 6 25
Oracle collections 15 16
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now