Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle - Issue with WHERE clause

Posted on 2013-02-02
9
Medium Priority
?
475 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 71

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 500 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 71

Assisted Solution

by:Qlemo
Qlemo earned 500 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 500 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

721 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