Solved

case expression problem

Posted on 2013-01-16
5
444 Views
Last Modified: 2013-01-17
/* 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;
0
Comment
Question by:stirus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 100 total points
ID: 38784788
what doesn't work?

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


please provide sample data and expected results
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 38784793
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
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 50 total points
ID: 38784811
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
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 50 total points
ID: 38786066
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
 

Author Closing Comment

by:stirus
ID: 38787546
My error.  comparing two unlike fields.  thanks for your response.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 79
upgrading Oracle 10g/ 11g / 11g R2 to Oracle 12c 25 89
Oracle Nested table uses ? 2 44
how to tune the query 17 56
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question