We happen to have a list of accounts that we need to have in our where statement that are in the thousands. Webfocus does not seem to allow that many accounts in a where statement.

We thought we would write the accounts out to a file and then join to the file doing an inner join and would therefore drop any accounts we don't need. I have 2 questions ...
1. is there a different way we should handle this that would be recommended?
2. I tried putting the accounts in a txt file and then I created a mas with a suffix=fix but it does not seem to find the accounts. It does not give me an error, it just says 0 records in table. Should the extension be something other than txt? I've tried searching the web for file format, but I'm not finding anything on this. I do have the file in the same directory as the mas.

Thanks.

Did you include a filedef statement?

When we import files, we must:

1. Create a master file description (like you did)
2. Make sure the file is sorted
3. Modify the read/write access
4. Include a FILEDEF statement pointing to the location of the data file.

This may sound silly not knowing your data but is the "NOT IN" list smaller and easier to deal with? You have thousands IN the list but do you have only a few NOT IN the list? The opposite values may be something to try.

I'm trying the filedef statement now.

The not in list is much bigger. So, that wasn't an option.

I'm getting data now. I put in a filedef and also changed the format of the file to .dat Thanks.

More than happy to help.

Instead of doing the JOIN, you could just read your text file in the WHERE. You would still need the filedef for your file, then you would code your WHERE with IN FILE filename instead of the normal WHERE expression.

If your account file is called ACCTFILE.TXT and the field you're looking for is ACCOUNT, then this is what it looks like:

First you still need the FILEDEF

FILEDEF ACCTFILE DISK E:\ACCTFILE.TXT

or whatever the filedef for your platform is

Then when you're ready for your WHERE it goes like this

WHERE ACCOUNT IN FILE ACCTFILE

The WHERE IN FILE has much larger limitations than the regular WHERE has (the documentation says file size limit is 16,000 bytes). If you exceed the 16,000 byte file size, you can change the WHERE to IF. For IF, the file can include 32,767 literals. The values in the file must be in single quotes if they contain blanks or mathematic operators.

You could also build your list of accounts in a subquery, hold that output as a FORMAT ALPHA file and then use your held file in the WHERE IN FILE statement. This would eliminate the need to create/maintain a .mas and .txt file and would make your ACCOUNTS list very dynamic.

I'm not sure which would be more efficient, the JOIN or the WHERE. It probably depends on your platform. This is what we LOVE about WebFOCUS. There always seems to be several ways to accomplish what you need. Hope this gives you some other options.

Deb

Pam,

Here is another alternative method using MATCH FILE syntax. This may be less efficient but simpler code. I used the CAR file to demonstrate the technique.

-* Create your list of accounts using this
TABLE FILE CAR
SUM CNT.COUNTRY
BY COUNTRY
WHERE COUNTRY NE 'ENGLAND' OR 'JAPAN'
ON TABLE HOLD AS THELIST
END

-* Create the final DATA file using this
MATCH FILE THELIST
SUM E02
BY COUNTRY
RUN
FILE CAR
PRINT CAR MODEL BODYTYPE
BY COUNTRY
AFTER MATCH HOLD AS FINALDAT OLD
END

-* This shows the end result
TABLE FILE FINALDAT
PRINT *
END