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,
WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 19, 2019, 11:12 AM
BabakNYC
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
WebFOCUS 8206, Unix, Windows
February 19, 2019, 11:54 AM
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 FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
February 19, 2019, 12:12 PM
Shingles
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!
WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 19, 2019, 01:31 PM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
February 19, 2019, 01:38 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
February 19, 2019, 07:27 PM
Addy
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.
WF 8.2.04 Windows/Unix All Formats In Focus since 2006
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!
WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 21, 2019, 03:15 PM
Shingles
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!
WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 22, 2019, 07:43 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
February 22, 2019, 08:38 AM
Joanna Swiggett
I just wanted to point out that SET ASNAME=ON should be SET ASNAMES=ON
WebFOCUS 8.2.01M
February 22, 2019, 10:41 AM
Shingles
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
WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 22, 2019, 10:54 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
February 22, 2019, 11:14 AM
Shingles
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.