RE: SQL - how to create NOT variable length fields in a SELECT stmt

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.