Case Statement in SQL Delminted Query

metalteck
metalteck used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
is this for oracle?

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

Author

Commented:
Yes, its for oracle and left padded is ok.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
LPAD( case expression ... end, 20 )

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

RPAD() is for right padding
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

David VanZandtOracle Database Administrator III

Commented:
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.

Author

Commented:
@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.
awking00Information Technology Specialist

Commented:
What is the data type and scale of emp.emp_status?

Author

Commented:
Emp Status is Char and 2 byte
awking00Information Technology Specialist

Commented:
So should those values show up at positions 1:2 or 19:20 for your vendor?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial