Solved

Case Statement in SQL Delminted Query

Posted on 2016-09-09
9
73 Views
Last Modified: 2016-09-15
I have the following case statement in my sql delimited query:
       ||(CASE
          WHEN emp.emp_status in ('FB','PN','PB','LW','LP','LS','LT','LE','LI','WA') THEN 'A'
          WHEN emp.emp_status in ('LF','LM','LO','LU','LA','LR') THEN 'L'
           ELSE null
          END

Although the result is just 1 character, per the vendor spec, it needs to be a length of 20.
How would I write that?
0
Comment
Question by:metalteck
[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
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41791992
is this for oracle?

and, do you want the result to be left padded, right padded, centered, other?
0
 

Author Comment

by:metalteck
ID: 41792189
Yes, its for oracle and left padded is ok.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41792267
LPAD( case expression ... end, 20 )

 By default lpad will add spaces up to the wanted length.

RPAD() is for right padding
0
CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

 
LVL 23

Expert Comment

by:David
ID: 41792305
Hi MetalTech, I am simply curious about the business rule behind this fixed length field. It stirs memories of twentieth-century COBOL. Thanks for any insight.
0
 

Author Comment

by:metalteck
ID: 41792794
@dvz, its funny that you mention Cobol. the original program is written in program, but the specifics the vendor has asked for regarding the extract, I'm forced to use oracle sql.
The vendor's specs require that each specified field have a length as well as start and end position.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41794453
What is the data type and scale of emp.emp_status?
0
 

Author Comment

by:metalteck
ID: 41794457
Emp Status is Char and 2 byte
0
 
LVL 32

Expert Comment

by:awking00
ID: 41794857
So should those values show up at positions 1:2 or 19:20 for your vendor?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

635 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