Home » SQL & PL/SQL » SQL & PL/SQL » Items validation (Oracle 8i)
Items validation [message #362329] Mon, 01 December 2008 17:52 Go to next message
Messages: 1
Registered: December 2008
Junior Member
I've got a Table "A" which has a column of "ITEMS"
I've got another Table "B" which has a column of "RULES"

The rules column includes invalid chars for ITEMS that we want to exclude (i.e. %IR1%, %-01% %-02%)

How would I return results which give all ITEMS that do not contain any invalid chars (outlined in the RULES column).

Little rusty with my SQL so hopefully I've exlpained it properly.
I know if I hardcode the rules it would probaly make my life easier.



[Updated on: Mon, 01 December 2008 18:13]

Report message to a moderator

Re: Items validation [message #362330 is a reply to message #362329] Mon, 01 December 2008 18:17 Go to previous message
Barbara Boehmer
Messages: 8609
Registered: November 2002
Location: California, USA
Senior Member
It would make things clearer if you provided create table and insert statements for sample data and the results you want based on that data. Assuming that the values between %'s that you provided are individual entries in rows of a rules column that are intended to be used for comparison using like, then something like this would work:

SELECT a1.items
FROM   a a1
       (SELECT a2.items
        FROM   a a2, b
        WHERE  a2.items LIKE rules
        AND    a2.items = a1.items);

or perhaps more efficiently:

SELECT a.items
FROM   a, b
WHERE  a.items LIKE b.rules (+)
AND    b.rules IS NULL

[Updated on: Mon, 01 December 2008 18:21]

Report message to a moderator

Previous Topic: SQL query efficiency
Next Topic: Spooling Output Issue
Goto Forum:

Current Time: Mon Oct 24 01:03:17 CDT 2016

Total time taken to generate the page: 0.08267 seconds