José Perez
asked on
Whats wrong with this query?
I have a query:
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 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%'
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%'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT DISTINCT SUBSTR(sp1.group_display_n ame, 0, instr(sp1.group_display_na me,'-')-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_n ame, 0, instr(sp1.group_display_na me,'-')-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.
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_n
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.
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.
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.
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.
>> 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?
"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.
LIKE 'contr%' should be sufficient and if you create an index on group_name then it should improve the speed dramatically.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ok, fair point. We agree indexing is needed.
ASKER
Thanks!
ASKER
More than happy with all your answers, thank you all.
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_n
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_n
from (select '7657567_controlling' as group_display_name from dual) sp1
returns NULL