Solved

Case Statement in SQL Delminted Query

Posted on 2016-09-09
9
66 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
9 Comments
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ODBC in excel 2016 in Windows 10 via VBA 16 85
sql query to calculate avaerage 21 41
Get number of Files in Directory and Sub Directories 2 42
Mysql Left Join Case 10 56
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

803 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