?
Solved

Case Statement in SQL Delminted Query

Posted on 2016-09-09
9
Medium Priority
?
76 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 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

719 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