Link to home
Start Free TrialLog in
Avatar of José Perez
José PerezFlag for Chile

asked on

Whats wrong with this query?

I have a query:
SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1,
  dm_group_rp rp1
WHERE sp1.group_name LIKE '%controlling%'
OR sp1.group_name LIKE '%contributing%'

Open in new window


that retrieves the following list:

7657567_controlling
456456_contributing
28531_controlling
090_contributing

but when I changed it to mix both filters '%_contr%', it retrieves nothing as a result... I am trying to get the very same result on the new query:

SELECT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1,
  dm_group_rp rp1
WHERE sp1.group_name LIKE '%_contr%'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is an antique way of specifying the FROM clause:

FROM dm_group_sp sp1,  dm_group_rp rp1

the danger of this old fashioned syntax is you are generating a Cartesian product of those 2 tables which may be quite unexpected.

The query you provided in the question does not reference the table dm_group_rp anywhere except in the FROM clause. So, I would simply remove it; unless of course you haven't supplied the whole query and there is a need for that table.

Then, you need to escape the underscore as identified by sdstuber.

SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1
WHERE sp1.group_name like '%\_contr%' escape '\'

-----------------
nb IF sp1.group_display_name does not contain a hyphen you would get a null returned.
e.g. this query

select SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
from (select '7657567_controlling' as group_display_name from dual) sp1

returns NULL
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1
WHERE sp1.group_name like '%\_contr%' escape '\'
;

Are you saying you tried the escaped version (above) and it still returns no rows?

Personally I don't see the advantage and would just use the two strings as you were doing

SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1
WHERE ( sp1.group_name LIKE '%controlling%'
          OR sp1.group_name LIKE '%contributing%'
             )
;

but as you see above I also like to use parentheses. I believe I have also commented on another very similar question from you.  On that question I have used the same approach to your where clause and simplified the table joins.
sorry @pcelba got myself confused.

the unescaped underscore is treated as a single position wildcard, so a character HAS TO exist for that location whereas % is any number (or no) characters.
Avatar of José Perez

ASKER

mm fom comments above.... if I use 2 'Likes':

LIKE '%controlling%' OR sp1.group_name LIKE '%contributing%'

Is the same as using 1 like that merge both?

LIKE '%contr%'

???
p.s. I am trying to decrease the time this query consumes for 3M rows database.
Avatar of Sean Stuber
Sean Stuber

>> know the group name does not begin with such character

"need" -no, it might not be strictly necessary depending on the data, but doing that allows for exact searching for the underscore character explicitly rather than wildcarding and hoping you find it   Not "-contr",  not "contr",  not "@contr", but only "_contr".


>>> p.s. I am trying to decrease the time this query consumes for 3M rows database.  

we know, but if your results aren't correct does it matter how fast you return them?
I wrote "does not begin with such character"... It should have been "it begins with c character" more accurately.

LIKE 'contr%'  should be sufficient and if you create an index on group_name then it should improve the speed dramatically.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, fair point. We agree indexing is needed.
Thanks!
More than happy with all your answers, thank you all.