Re: joining 3 files

[Peter Dow <petercdow@xxxxxxxxx>]

Hi Jerry,

Instead of CAST, use DIGITS (if you need leading zeros) or CHAR, e.g.

select * from file1 A left join file2 B on digits(A.FLD1) = B.FLD2

or

select * from file1 A left join file2 B on char(A.FLD1) = B.FLD2

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx

/
On 6/22/2022 11:23 AM, jerry ven wrote:
> basically i need casted result of fld2 in such a way that fld2 is of 9
> length and it is of data type character and i need to extract  6 numeric
> digits which are in consecutive order in this field and they can occur at
> any position in this field (fld2) of file2.
> So how  to get that collective result from this sql  query so that file1
> ,file2,file3 could be joined all together.
>
>
> Thanks.
>
>
>
>
> On Wed, 22 Jun 2022 at 23:33, jerry ven<jerryven95@xxxxxxxxx>  wrote:
>
> > I tried it like below( just for example):-
> >
> >
> > select * from file1 A left join file2 B  ON A.FLD1 = CAST(B.FLD2  AS INT)
> >
> > But getting this error 'selection error involving field *N
> > When checking it in job log then came to know 'select or omit error on
> > field cast(Translate (LIB NAME.fld2, *unnamed table)
> >
> > here FLD2's length is 9 (it's data type is
> > CHARACTER  type)
> > and
> > FLD1 is of 6 length(with Decimal Data Type)