Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problems with joining....

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Problems with joining....
 Login/Join
 
Platinum Member
posted
Hi everyone,

I really hope someone can help here as I am going slightly mad trying to figure this one out!

I will try to simplify as much as possible:

I have built a hold file H_PERIOD which contains a distinct list of week numbers / year number from a given period:

Eg:
WEEK_NO YEAR_NO
20 6
21 6
22 6
23 6
24 6

I have a another hold file, H_PRODUCT that contains a list of products, but in this example just 1. I am joining this to a sales summary file, SALES, that has WEEK_NO and YEAR_NO fields.

My objective is to take list of products to the sales summary file and extract only the records that match the periods in my H_PERIOD file.

JOIN
PRODUCT_NO IN H_PRODUCT TO ALL
PRODUCT_NO IN SALES AS JO
END
JOIN
WEEK_NO AND YEAR_NO IN H_PRODUCT TO
WEEK_NO AND YEAR_NO IN H_PERIOD AS J1
END

The first join takes my 1 product and finds 200 sales records for many months across many years. I then only want see the 5 records out of 200 that match the periods in my H_PERIOD file.

However I am always getting records records for every period, and not just those that appear in my H_PERIOD file. It's behaving like a left outer join, and yet I do not have SET ALL=PASS on?

I hope someone out there can understand this and help me out!

Many thanks as usual.

Mark.


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Guru
posted Hide Post
Mark

Join H_PRODUCT to Sales and hold. Now join the hold to H_PERIOD

JOIN
PRODUCT_NO IN H_PRODUCT TO ALL
PRODUCT_NO IN SALES AS JO
END

TABLE FILE H_PRODUCT
PRINT
*
BY WEEK_NO AND YEAR_NO NOPRINT
ON TABLE HOLD AS PROD_BY_WEEK
END

JOIN
WEEK_NO AND YEAR_NO IN H_PERIOD TO
WEEK_NO AND YEAR_NO IN PROD_BY_WEEK AS J1
END

TABLE FILE H_PERIOD
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END

Hope that helps.


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Guru
posted Hide Post
The second join should read

JOIN
WEEK_NO AND YEAR_NO IN H_PERIOD TO ALL
WEEK_NO AND YEAR_NO IN PROD_BY_WEEK AS J1
END

To make sure you don't have the SET ALL=PASS on

Start query with SET ALL=OFF


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Platinum Member
posted Hide Post
There's another way to do this, I think. Might seem a little counterintuitive. Turn the first join around (and remove ALL from the join). And as Glenda noted, you want to be sure to SET ALL=OFF

JOIN
PRODUCT_NO IN SALES TO
PRODUCT_NO IN H_PRODUCT AS JO
END
JOIN
H-PRODUCT.WEEK_NO AND H-PRODUCT.YEAR_NO IN SALES TO
WEEK_NO AND YEAR_NO IN H_PERIOD AS J1
END

Then, of course:

TABLE FILE SALES
etc

Joining for SALES to H_PRODUCT isn't exactly efficient, but it might be better than the hold. Might not. Depends on your data, I think.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
Platinum Member
posted Hide Post
Thanks for your input all Smiler

DWF, I did try the joins in the order you suggested, however the sales file is very large and this did seem to be a slower approach.

Linda, I have changed my code to build a hold file between the two joins but I am having difficulties with the last part of the code:

JOIN
WEEK_NO AND YEAR_NO IN H_PERIOD TO
WEEK_NO AND YEAR_NO IN PROD_BY_WEEK AS J1
END

TABLE FILE H_PERIOD
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END

My H_PERIOD file has 5 records for 5 months.
My PROD_BY_WEEK file has 200 records for 10 products for over many years.
However my result only contains 10 records - it is only matching the first row (first month) on the H_PERIOD file. I want to be seeing 50 records (5 months x 10 products).

It appears to be doing a 1 to Many join, instead of a Many to Many join.

I have tried turning the last join around, but then I get no records!


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Expert
posted Hide Post
Mark,

How many periods do you need to match on?

If there are only a few, then instead of using a join use the WHERE fieldname IN FILE filename syntax. As your requirement is the combination of two columns then you will have to combine them in a COMPUTE or DEFINE. The question above relates to the fact that the limit for FILE look ups is 32767 bytes.

I would advise that you hold your year in a four digit format - remember that is why we had the alleged Y2K problem Music

If you have the GGSALES files then this is an example -

SET HOLDLIST = PRINTONLY
DEFINE FILE GGSALES
 YEAR/A4 = EDIT(DATE,'9999');
 BASE_YEAR/I8YYMD = EDIT(EDIT(DATE,'9999$') || '0101');
 BASE_DAY/I8 = DAYMD(BASE_YEAR,'I8');
 DAY_NUM/I8 = DAYMD(DATE,'I8') - BASE_DAY + 1;
 WEEKI/I2 = (DAY_NUM / 7) + 1;
 WEEK/A2 = IF WEEKI LT 10 THEN '0' || EDIT(EDIT(WEEKI),'$9') ELSE EDIT(WEEKI);
END
TABLE FILE GGSALES
     BY YEAR
	 BY WEEK
WHERE DATE FROM '19960401' TO '19960731'
ON TABLE SAVE AS MYDATES
END

DEFINE FILE GGSALES ADD
  YRWK/A6 = YEAR || WEEK;
END
TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY ST
BY YEAR
BY WEEK
WHERE DATE FROM '19960101' TO '19971231'
IF YRWK EQ (MYDATES)
END 


Good luck

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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Hi Tony,

This is great! I am quite new to WebFocus and none of our programs at work use the IN FILE syntax.

I am now able to query our large sales file using this IN FILE syntax:

TABLE FILE SALES
....
IF PRODUCT_NO EQ (H_PRODUCT)
IF YEAR_WEEK EQ (H_PERIOD)

This appears to be SO much faster than joining the tables together and I am getting just the results I expected!

Can you explain this limit of 32767 bytes a bit more? Is it the total size of the file that I will be sub-selecting to, eg H_PRODUCT?

Thanks again Smiler


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Expert
posted Hide Post
Hi Mark,

Glad you found a new possibility using IN FILE.

The limit is, indeed, the file you are using to look-up as you suggest. Why the limit? I do not know but would hazard a guess that it is a page file limitation or something of that ilk as it is basically 32K.

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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problems with joining....

Copyright © 1996-2020 Information Builders