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] McGyver Technique
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] McGyver Technique
 Login/Join
 
Master
posted
So I'm feeling a bit embarrassed as I've done this hundreds of times, but I went to put together a quick proof of concept of the McGyver Technique to help someone with an issue... And I can't get it to display properly. Can someone tell me what I'm doing wrong, because pretty sure this would have worked pre wf8.

DEFINE FILE CAR
BLANK/A1 WITH COUNTRY='1';
END
TABLE FILE CAR
SUM SALES RETAIL_COST
BY COUNTRY
BY BLANK
ON TABLE HOLD AS MY_HOLD FORMAT ALPHA
END


TABLE FILE MY_HOLD
SUM SALES RETAIL_COST
BY BLANK
BY COUNTRY
ON TABLE HOLD AS MY_HOLD2 FORMAT ALPHA
END

JOIN
INNER MY_HOLD.MY_HOLD.BLANK IN MY_HOLD TAG H1 TO MULTIPLE
MY_HOLD2.MY_HOLD2.BLANK IN MY_HOLD2 TAG H2 AS J0
END

TABLE FILE MY_HOLD
SUM H2.SALES H2.RETAIL_COST
BY H1.COUNTRY
BY H2.COUNTRY
ON TABLE PCHOLD FORMAT HTML
END  


For this result set I would expect to get 25 rows, instead I'm getting 5. England in H1.COUNTRY and then each country in H2.

PAGE 1 
 
COUNTRY COUNTRY SALES RETAIL_COST 
ENGLAND ENGLAND 12000 45,319 
        FRANCE   0    5,610 
        ITALY   30200 51,065 
        JAPAN   78030 6,478 
        W GERMANY 88190 64,732 
  


When I look at the error messages, I'm getting the below error for each value after England.
quote:
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : MY_HOLD2/


I thought FOC1072 was just a warning and should allow me to create my cartesian join. What did I forget about? or is this a result of code tightening?

This message has been edited. Last edited by: eric.woerle,


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Master
posted Hide Post
DEFINE FILE CAR
BLANK/A1 WITH COUNTRY='1';
END
-*
TABLE FILE CAR
SUM SALES RETAIL_COST
BY BLANK
BY COUNTRY
ON TABLE HOLD AS MY_HOLD FORMAT ALPHA
END
-*
TABLE FILE MY_HOLD
SUM SALES RETAIL_COST
BY BLANK
BY COUNTRY
ON TABLE HOLD AS MY_HOLD2 FORMAT FOCUS INDEX BLANK
END
-*
JOIN
BLANK IN MY_HOLD TAG H1 TO ALL
BLANK IN MY_HOLD2 TAG H2 AS J0
END
-*
TABLE FILE MY_HOLD
SUM H2.SALES H2.RETAIL_COST
BY H1.COUNTRY
BY H2.COUNTRY
ON TABLE PCHOLD FORMAT HTML
END    

Yields 25 rows.

Basically, I only changed the cross-reference file from physical sequential to indexed.

If you need/want the cross-reference file to be physical sequential, you'll usually see parent/child attributes in the .mas.

See for example:
http://forums.informationbuild...707040786#3707040786

This message has been edited. Last edited by: David Briars,
 
Posts: 788 | Registered: April 23, 2003Reply With QuoteReport This Post
Master
posted Hide Post
I so much prefer SQL statements for this.

More control, and no pesky index, alpha or whatever. Just focus files.


DEFINE FILE CAR
BLANK/A1 WITH COUNTRY='1';
END

TABLE FILE CAR
SUM SALES RETAIL_COST
BY	BLANK
BY COUNTRY
ON TABLE HOLD AS H01
END
-*
TABLE FILE CAR
SUM SALES RETAIL_COST
BY	BLANK
BY COUNTRY
ON TABLE HOLD AS H02
END

SQL
SELECT			H01.*,
				H02.*
FROM			H01
INNER JOIN		H02
ON	H01.BLANK = H02.BLANK
;
TABLE HOLD AS H_CARTESIAN
END

TABLE FILE H_CARTESIAN
PRINT *
ON TABLE PCHOLD FORMAT HTML
END    




And with "control" I mean. Consider these extra join-conditions

All joins expect with own : AND H01.COUNTRY <> H02.COUNTRY

no double records ( e.g. only alphabetically higher countries ) : AND H01.COUNTRY < H02.COUNTRY



Greets,


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Better yet, in SQL you do not need the BLANK column as the default join is a cartesian product:
TABLE FILE CAR
SUM SALES RETAIL_COST
BY COUNTRY
ON TABLE HOLD AS H01
END

TABLE FILE CAR
SUM SALES RETAIL_COST
BY COUNTRY
ON TABLE HOLD AS H02
END

SQL
SELECT	H01.*, H02.*
FROM	H01, H02
;
TABLE HOLD AS H_CARTESIAN
END

TABLE FILE H_CARTESIAN
PRINT *
ON TABLE PCHOLD FORMAT HTML
END


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1655 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Basically, I only changed the cross-reference file from physical sequential to indexed.


Thought I tried that because I saw that mentioned in another post. I guess I missed something. Thanks David!.

I could have sworn back in 7.x you didn't need to do that though. Maybe its just code tightening.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] McGyver Technique

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