Solved

Case Statement in SQL Delminted Query

Posted on 2016-09-09
9
70 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 48

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

756 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