Re: Use like clause more then once

On May 29, 10:59 pm, Jeremy Smith <godto..._at_hotmail.com> wrote:
> I need the and in the statement and I'm sorry for not providing all of
> the info.
>
> services table would aslo have this insert.
>
> SQL> insert into services values (1, 'TSM');
>
> 1 row created.
>
> SQL> select * from services;
>
> ID DISPLAY_NAME
> ---------- --------------------------------------------------
> 1 MICROSOFT EXCHANGE
> 1 TSM
> 2 TSM
> 3 MICROSOFT EXCHANGE
> 4 SOMETHING ELSE
>
> Then run
>
> SQL> select id, name
> 2 from servers
> 3 where id IN (select id
> 4 from services
> 5 where display_name like '%MICROSOFT EXCHANGE')
> 6 and id in (select id
> 7 from services
> 8 where display_name like '%TSM%')
> 9*
> SQL> /
>
> and you get
>
> ID NAME
> ---------- --------------------------------------------------
> 1 server1
>
> But if I did my update qury to reduce scanning the services table
> twice.
>
> SQL> select s.id, s.name
> 2 from servers s, services sr
> 3 where s.id = sr.id
> 4 and sr.display_name like '%MICROSOFT EXCHANGE'
> 5* and sr.display_name like '%TSM%'
> SQL> /
>
> I get
>
> no rows selected
>
> So if I do a LIKE clause with an AND it comes back no rows, when the
> condition exists.
>
> Thanks for any help with this.

Let's try an experiment:
CREATE TABLE T1 (
  ID NUMBER(10),
  DISPLAY_NAME VARCHAR2(20));

INSERT INTO T1 VALUES (1,'MICROSOFT EXCHANGE');
INSERT INTO T1 VALUES (1,'TSM');
INSERT INTO T1 VALUES (2,'TSM');
INSERT INTO T1 VALUES (3,'MICROSOFT EXCHANGE');
INSERT INTO T1 VALUES (4,'SOMETHING ELSE');

COMMIT; We now have a table with five rows to experiment with, let's see if analytical functions can help:

SELECT
  ID,
  DISPLAY_NAME,
  COUNT(DISTINCT DISPLAY_NAME) OVER (PARTITION BY ID) COUNT_DISTINCT FROM
  T1
WHERE
  DISPLAY_NAME LIKE '%MICROSOFT EXCHANGE%'   OR DISPLAY_NAME LIKE '%TSM%';         ID DISPLAY_NAME COUNT_DISTINCT

---------- -------------------- --------------
         1 MICROSOFT EXCHANGE                2
         1 TSM                               2
         2 TSM                               1
         3 MICROSOFT EXCHANGE                1

Now, let's eliminate those rows with COUNT_DISTINCT not equal to 2: SELECT DISTINCT
  ID
FROM
  (SELECT
    ID,
    DISPLAY_NAME,
    COUNT(DISTINCT DISPLAY_NAME) OVER (PARTITION BY ID) COUNT_DISTINCT   FROM
    T1
  WHERE
    DISPLAY_NAME LIKE '%MICROSOFT EXCHANGE%'     OR DISPLAY_NAME LIKE '%TSM%')
WHERE
  COUNT_DISTINCT=2;         ID

         1

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed May 30 2007 - 09:12:55 CDT