Solved

Oracle - Issue with WHERE clause

Posted on 2013-02-02
9
472 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax: How to force case sensitive query? 2 45
error in my cursor 5 41
SSRS: Why is Visual Studio stripping these properties? 2 23
SQL Recursion 6 20
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

828 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