Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Use an Alias in HOLD file
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Use an Alias in HOLD file
 Login/Join
 
Platinum Member
posted
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
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5617 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 74 | Location: UK | Registered: September 17, 2018Reply With QuoteReport This Post
Master
posted Hide Post
Check out the docs on Controlling Attributes in HOLD Master Files


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 493 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
I just wanted to point out that SET ASNAME=ON should be SET ASNAMES=ON


WebFOCUS 8.2.01M
 
Posts: 9 | Location: Charlotte, NC | Registered: December 19, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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!


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Use an Alias in HOLD file

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.