Metalteck
asked on
Case Statement in SQL Delminted Query
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?
||(CASE
WHEN emp.emp_status in ('FB','PN','PB','LW','LP',
WHEN emp.emp_status in ('LF','LM','LO','LU','LA',
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?
ASKER
Yes, its for oracle and left padded is ok.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
@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.
The vendor's specs require that each specified field have a length as well as start and end position.
What is the data type and scale of emp.emp_status?
ASKER
Emp Status is Char and 2 byte
So should those values show up at positions 1:2 or 19:20 for your vendor?
and, do you want the result to be left padded, right padded, centered, other?