So... my problem is this. I have two hold files HOLD1 and HOLD2. HOLD1 has a column named COLUMN and so does HOLD2. If I join HOLD1 and HOLD2, and then query COLUMN from HOLD2 from the resulting joined table (HOLD1), I only end up getting values from COLUMN in HOLD1. I understand that webfocus looks for the first column name and that is why it is taking COLUMN from HOLD1, but I can't figure out a way around this.

COLUMN from HOLD1 and HOLD2 are both needed, but I can't seem to get values from COLUMN in HOLD2 after the join is made. I tried to create aliases by way of using 'AS' in HOLD1 and HOLD2 (BY COLUMN AS 'HOLD1_COLUMN' for HOLD1 and BY COLUMN AS 'HOLD2_COLUM' for HOLD2), but the aliases don't stick. I only see COLUMN when I spit out the results of the join.

Can someone point me in the right direction here?

Thank you!

This message has been edited. Last edited by: FP Mod Chuck,

When I HOLD data for this type of situation, I always add meaningful titles to each column that'll make them unique and:
SET ASNAME=ON
SET HOLDLIST=PRINTONLY

Also consider using TAGs -

JOIN field IN TABLE1 TAG T1 TO [unique/multiple] field IN TABLE 2 TAG T2 AS J1

TABLE FILE TABLE1
BY T1.field
BY T2.field
etc...


T

Thanks for the quick replies... you guys rock.

BabakNYC... will SET HOLDLIST=PRINTONLY make it so that I can only use PRINT as a verb? Could I still use SUM and BY?

In the meantime, I'll try the TAG approach.

Thank you!

quote:

Originally posted by Shingles:
BabakNYC... will SET HOLDLIST=PRINTONLY make it so that I can only use PRINT as a verb? Could I still use SUM and BY?

Yes.
PRINTONLY means keep displayed fields only no matter if it's a BY, ACROSS, SUM, PRINT or COMPUTE (for COMPUTE only the resulting field is kept, not the internal references).
As per example, a field with a NOPRINT will not be included using the HOLDLIST=PRINTONLY

quote:

will SET HOLDLIST=PRINTONLY make it so that I can only use PRINT as a verb? Could I still use SUM and BY?

Shingles, it may be worth your while to check out the documentation to understand what it does.

quote:

Originally posted by Tony A:
Also consider using TAGs -

JOIN field IN TABLE1 TAG T1 TO [unique/multiple] field IN TABLE 2 TAG T2 AS J1

TABLE FILE TABLE1
BY T1.field
BY T2.field
etc...


T

In your case TAG will solve your problem for sure. If you dont want to use tag then go for TableName.FieldName

This will come in handy if you are using cluster master files and are not specifying your joins in the code.

Check out the docs on Controlling Attributes in HOLD Master Files

Just a quick update... the TAG route didn't work for me. Webfocus wasn't able to find T2.COLUMN.

I went with the PRINTONLY route and it seems good so far.

Thanks again folks!

Follow up question... I'm not sure if this warrants a new thread, but I wanna do what I can to not flood the forums...

So, I'm using PRINTONLY and I am now able to see the aliases I used after the join... to recap my code looks something like this:

   
SET ASNAME=ON
SET HOLDLIST=PRINTONLY

TABLE FILE WHATEVER
BY COLUMN AS 'HOLD1_COLUMN'
BY UNIQUE_KEY
ON TABLE HOLD AS HOLD1
END

TABLE FILE SOMETHING
BY COLUMN AS 'HOLD2_COLUMN'
BY UNIQUE_KEY
ON TABLE HOLD AS HOLD2
END

JOIN LEFT_OUTER UNIQUE_KEY IN HOLD1 TO ALL UNIQUE_KEY IN HOLD2 AS J11

TABLE FILE HOLD1
PRINT * ON TABLE SET PAGE-NUM NOPAGE ON TABLE SET LINES 999999
END

The resulting HOLD1 table shows HOLD2_COLUMN as . when a join isn't made (but shows the correct information when a join is made). Which I get... its a null record. But I would like for HOLD2_COLUMN to show as 0 instead.

I've tried to create some DEFINE fields to accomplish this...

NEW_HOLD2_COLUMN/D9.6 MISSING OFF = HOLD2_COLUMN;

NEW_HOLD2_COLUMN/D9.6 MISSING ON = HOLD2_COLUMN;

NEW_HOLD2_COLUMN/D9.6 = IF HOLD2_COLUMN EQ MISSING THEN 0 ELSE HOLD2_COLUMN;

NEW_HOLD2_COLUMN/D9.6 = IF HOLD2_COLUMN NE MISSING THEN 0 ELSE HOLD2_COLUMN;

Now admittedly... I got a little desperate, and I just started typing stuff that didn't even make sense to me, but none of those worked. Any tips here?

Thanks again folks!

Your issue is probably not because of missing HOLD2_COLUMN value but a missing child
Try this instead

JOIN
 LEFT_OUTER UNIQUE_KEY IN HOLD1 TAG T1
     TO ALL UNIQUE_KEY IN HOLD2 TAG T2 AS J11
END

DEFINE FILE HOLD1
NEW_HOLD2_COLUMN/D9.6 = IF T2.UNIQUE_KEY EQ MISSING THEN 0 ELSE HOLD2_COLUMN;
END

TABLE FILE HOLD1
PRINT *
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET LINES 999999
END

Or take a look at SET ALL options, MULTIPATH, SHORTPATH and JOIN definition
Handling a Missing Segment Instance
Selections Based on Individual Values
SHORTPATH
Joining Data Sources
And you can find much more documentation regarding JOIN and all the options

I just wanted to point out that SET ASNAME=ON should be SET ASNAMES=ON

Thanks Joanna...

Martin... That's not getting the expected results. I could use a MORE (which I just call a UNION) to get me zeroes. I've don't that plenty of times.

Thank you

Maybe having this added at the beginning of you code :
SET NODATA = 0


Or can you try this ?

SET NODATA = 0

MATCH FILE HOLD1
PRINT HOLD1_COLUMN
BY UNIQUE_KEY
RUN
FILE HOLD2
PRINT HOLD2_COLUMN
BY UNIQUE_KEY
AFTER MATCH HOLD AS MATCHDATA OLD
END
-RUN

DEFINE FILE MATCHDATA
NEW_HOLD2_COLUMN/D9.6 = IF HOLD2_COLUMN EQ MISSING OR 0 THEN 0 ELSE HOLD2_COLUMN;
END
TABLE FILE MATCHDATA
PRINT HOLD1_COLUMN
      HOLD2_COLUMN
      NEW_HOLD2_COLUMN
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET LINES 999999
END
-RUN

Thanks for the persistence Martin...

I did try the 'EQ MISSING OR 0' route before with no success. I forgot about 'SET NOTDATA = 0' though.

I did solve the problem with MORE, and I have some more pressing things to do at the moment. But when I get a few minutes, I'll try what you said.

Thank you again!