This worked great thanks!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Wednesday, March 28, 2012 3:45 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL - how to create NOT variable length fields in a SELECT stmt

I'd have simplified that a tad:

CHAR(case when client_act = 'S' then 'S' else ' ' end, 1) AS NEWFIELD1

(Actually, I'd forgo the use of the CHAR( ..., 1) too - overkill - but if
you want a guaranteed fixed-length variable, CHAR( ..., len) is your friend.

I cannot envision any circumstance in which that will not do what you want.
But maybe I missed a requirement?

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The minute you read something you can't understand, you can almost be sure
it was drawn up by a lawyer."
-- Will Rogers


Use CAST, as in :

CAST (value(max(case when client_act = ''S'' then ''S'' else '' ''
end),''
'') AS CHAR(1) ) AS NEWFIELD1

HTH,

Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
--




On Wed, Mar 28, 2012 at 3:27 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx>
wrote:

The output of new SQL fields (NEWFIELD1 for example) is variable
length.
How I force them to be 1 character fixed length with the select
statement?

select zoned(client_n,6,0) as client_n , value(max(case when
client_act = ''S'' then ''S'' else '' '' end),'' '') as NEWFIELD1
from clientact group by client_n order by client_n


^^^^^^^^



Thanks!


______________________________________________________________________
This outbound email has been scanned for all viruses by the
MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com

______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].