Re: Using SQL to obtain a 6 character field from a 3, 4, 5 or 6 character string

[CRPence <CRPbottle@xxxxxxxxx>]

Alan Shore wrote:
> Good morning all what I am trying to achieve sounds so familiar.
> I believe this was a discussion that happened a couple of years
> ago, but I cannot seem to find it. Probably searching for the
> wrong thing
>
> Anyway, heres my dilemma I have a file with a 10 character field
> (lets call it FIELDA) Within this FIELDA, I can have a 3, 4, 5,
> or 6 character string (not necessarily left adjusted) This string
> happens to be numeric (but for my purposes - it needs to remain 
> alpha) I need to convert this numeric string into a 6 character
> string with leading zeroes AND I need to create a view/index over
> this file using this new field as the key
>
> It being Monday morning, not having my 18th cup of coffee, bleary
> eyed from staying up too long after the super bowl etc. etc. I
> just cannot get my head around this, therefore, as always, any
> and all help would be MUCH appreciated

  One year ago something similar:
http://archive.midrange.com/midrange-l/200901/msg00843.html
  A couple years ago for an is_numeric topic:
http://archive.midrange.com/midrange-l/200708/msg00701.html

  If the data is and should be only unedited positive integer 
values [i.e. only digits, no decimals, negative indicators, currency 
symbol, etc.] with the remaining bytes being blank\space characters, 
then I would recommend the cast to numeric and then cast back to 
character using the DIGITS [as eventually ;-) given in a functional 
example from Luis].  That ensures the criteria of the data are met, 
such that when they are not, a mapping error will result which could 
prevent some GIGO scenarios.

  With such an index, either permanent or temporary [the latter 
could occur for query implementation], any data insert and update 
activity while that index is actively enforced must also meet those 
data characteristics.  Such an index can be created as a permanent 
SQL INDEX, only since v6r1.

  Seems to me, the column would best be modified to an integer, 
zoned, or packed decimal data type, with a check constraint to 
enforce any specific data limits.  If not, at least triggers to 
transform the data into a\this consistent form [left or right 
justified into the 10-byte field] to avoid the need to massage the 
data for the\each inquiry.

<code>
  create table c10 (c char(10))
  ;
  create trigger       c10_ib
  before insert on       c10
  referencing new as n
  for each row
  set c=digits(decimal(n.c, 6, 0))
  ;
  insert into      c10 values
    ('  123'),('    578901')
  ;
  select * from     c10
  ;
  : ....+....1
  : C
  : 000123
  : 578901
  : ********  End of data
</code>

Regards, Chuck