Re: joining 3 files

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)