Solved

Oracle - Issue with WHERE clause

Posted on 2013-02-02
9
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 70

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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
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 70

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 49

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

635 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