Hi All,

I have a file keyed as follows:

fldA, 2A
fldB, 2A
fldC, 2A
fldD, 2,0 numeric
fldE, 6,0 numeric

and a second file keyed by one 17A field where

fldA + fldB + fldC + fldD + fldE + 3spaces = the 17A key field.

What syntax do I use on my JOIN statement to link the two differing types of file key structures?

Thanks,
Rayden.

DEFINE FILE YOURFILE
-* To pad with leading zeroes...
a_fldD/A2 = EDIT(fldD);

-* To not pad...
a2_fldD/A2 = FTOA(fldD, '(Dc2)', a2_fldD);

-* Same idea with fldE

combined/A17 = fldA | fldB | fldC | a_fldD | a_fldE;

END

Now you should be able to join on the field called combined.

Sean

we don't know what kind of file you have?
is it focus?
is it a rdb?
fields with embedded blanks can be tricky

Thanks for the inpust so far.

To give you more detail both files are DB2 tables.

I have tried defining an A17 field concatenanted from the multiple fields in file1 but when I try to join on it to the other file I get this error:

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE: A_SID#
BYPASSING TO END OF COMMAND

A_SID# is the new field I defined in file1.

What have I missed?

Thanks,
Rayden.

The error message refers to file A_SID#, however you refer to field name A_SID#. Something isn't adding up. Can you post the JOIN statement that you are using, plus some of the relevant code leading up to that join?

Sean

Hi again,

Here is the define:

DEFINE FILE xxx
A_4/A2=EDIT(xxfl4,'99');
A_5/A6=EDIT(xxfl5,'999999');
A_6/A3=EDIT(xxfl6,'999');

A_SID#/A17=xxfl1|xxfl2|xxfl3|A_4|A_5|A_6;

SET ALL=ON
JOIN CLEAR *
JOIN xxx.A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0
END

When I got the last message I had the join like this:

SET ALL=ON
JOIN CLEAR *
JOIN A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0
END

Since qualifying the field with the file name I re-ran and got this error:

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
xxx.A_SID#
BYPASSING TO END OF COMMAND


I must be doing something dumb, but I have not had a lot of experience with this product.

Really appreciate your time and assistance.

Rayden.

Rayden,

Sorry, I misinterpreted the error message previously, but....

You need to close off your DEFINE FILE xxx with an END statement. Do you have the END statement in your source code?

Sean

Hi Sean,

I do have an END prior to the JOIN, there is some other define code also but not pertaining to the fields for the key to file2, so I omitted it in my POST.

As the xxx file is a DB2 table pulled into the metadata, does that limit the define capbilities, and only lets me use actual fields from xxx in the JOIN statement?

Thanks,

Rayden,

Rayden,

Hmmm. I don't use DB2, so I don't know if that's an issue or not. Anybody else know?

I'll try to dig after the Web conference if nobody finds anything.

Sean

quote:

JOIN xxx.A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0

When joining on a defined field, you need to specify WITH . That will signal that it's a defined field (in fact a yet-to-be-defined field), and also that the retrieval via the join must be refreshed whenever a new instance of the segment containing the real field is read.

JOIN xxx.A_SID# WITH xxfl6 IN xxx TO ALL yyy.fl17 IN yyy AS J0

quote:

JOIN xxx.A_SID# IN xxx TO ALL yyy.fl17 IN yyy AS J0

I do believe you need to add a WITH in your join. JOIN xxx.A_SID# WITH FLDA IN xxx TO ALL yyy.fl17 IN yyy AS J0
I've done it against DB2 using a defined field in the join.

Hi All,

I got it. I was doing 2 things wrong. First the JOIN has to be issued before the DEFINE, and secondly as j.g. stated I needed the WITH to let the JOIN know the field is to be defined.

Thanks to all for your input.

Back to work..

Rayden.