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.

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.

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

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.

Thanks for your input all

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!

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

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

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

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