×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
