case expression problem

/* this code works,  similar version below does not */
/* some issue with the substr in the case statement*/

select a.bl_id, a.max_bl_id, substr(max_bl_id,5,4), a.title, a.name, m.location, m.description, m.bc_buildcode,
case when a.title = m.bc_buildcode then 'same'
    else 'diff'
    end as "fields same or different"
from bl A
join locations m
on a.title = m.bc_buildcode
order by name;


/*this code does not.  code is the same exception for the 1st line of the case */

select a.bl_id, a.max_bl_id, substr(max_bl_id,5,4), a.title, a.name, m.location, m.description, m.bc_buildcode,
case when substr(max_bl_id,5,4) like substr(m.bc_buildcode,1,4) then 'same'
    else 'diff'
    end as "fields same or different"
from bl A
join locations m
on a.title = m.bc_buildcode
order by name;
stirusAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
what doesn't work?

wrong results? no results? error? keyboard catches fire?


please provide sample data and expected results
0
 
sdstuberConnect With a Mentor Commented:
one question comes to mind immediately though.

Why do you use LIKE in the second query?  Are their wildcards in your strings? If not, it's effectively the same = and should be written that way to be self-documenting
0
 
paquicubaConnect With a Mentor Commented:
sdstuber is right, since you're extracting 4 characters from each string, you don't need to use the LIKE operator. However, if you're planning to extract 4 characters from the first string and 3 characters from the second one, then you can do something like:


select a.bl_id, a.max_bl_id, substr(max_bl_id,5,4), a.title, a.name, m.location, m.description, m.bc_buildcode,
case when substr(max_bl_id,5,4) like substr(m.bc_buildcode,1,3)||'%' then 'same'
    else 'diff'
    end as "fields same or different"
from bl A
join locations m
on a.title = m.bc_buildcode
order by name;
0
 
Geert GConnect With a Mentor Oracle dbaCommented:
if you want case insensitive check, then add upper() too

select a.bl_id, a.max_bl_id, substr(max_bl_id,5,4), a.title, a.name, m.location, m.description, m.bc_buildcode,
  decode(upper(substr(max_bl_id,5,4))=upper(substr(m.bc_buildcode,1,4)),'same','diff') 
  "fields same or different"
from bl A
join locations m
on a.title = m.bc_buildcode
order by name; 

Open in new window

0
 
stirusAuthor Commented:
My error.  comparing two unlike fields.  thanks for your response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.