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
gman1
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.

Thanks,

Steve

[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: 8625
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
WHERE  NOT EXISTS
       (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: Sun Dec 04 16:28:26 CST 2016

Total time taken to generate the page: 0.08433 seconds