I thought this would work, but it doesn't - the compute always results in 'NO'. Am I losing my mind or can't we do this?

TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY NE 'W GERMANY'
ON TABLE SAVE AS S001
END
-RUN

TABLE FILE CAR
PRINT COUNTRY CAR MODEL SALES
COMPUTE FLAG1/A3 = IF COUNTRY IN FILE S001 THEN 'YES' ELSE 'NO';
END
-RUN

This message has been edited. Last edited by: Francis Mariani,

What if you put the compute in a DEFINE first Francis?

This doesn't work either:

TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY NE 'W GERMANY'
ON TABLE SAVE AS S001
END
-RUN

DEFINE FILE CAR
FLAG1/A3 = IF COUNTRY IN FILE S001 THEN 'YES' ELSE 'NO';
END

TABLE FILE CAR
PRINT COUNTRY CAR MODEL SALES FLAG1
END
-RUN

I can't seem to find a single example of this.

I think IN FILE requires a master in order to find/read the file.
but then again....its been a long week.

how about

DEFINE FILE CAR
FLAG0/I2 = DECODE COUNTRY(S001 ELSE 1);
FLAG1/A3=IF FLAG0 EQ 0 THEN 'NO' ELSE 'YES';
END

Frank, thank you! While you were typing that example out, I also found it in the documentation under "DECODE: Decoding Values". The solution seems counterintuitive but it works, so thanks very much. Here's my example code corrected:

TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY NE 'W GERMANY'
ON TABLE SAVE AS S001
END
-RUN

DEFINE FILE CAR
NOT_WHAT_I_WANT/I1 = DECODE COUNTRY (S001 ELSE 1); 
FLAG1/A3 = IF NOT_WHAT_I_WANT EQ 1 THEN 'NO' ELSE 'YES';
END

TABLE FILE CAR
PRINT COUNTRY CAR MODEL SALES FLAG1
END
-RUN

But I wonder what happens if you say "EQ 'W GERMANY' or 'ITALY'
I think the space between W G will give some problems.

You're right! The values in the file must have single quotes. Lucky for me, my real-world file has no blanks.

I spoke too soon I can't get this to work

I'm sure you can Francis

What is the problem? number of records? or spaces...

The error message I'm getting is quite misleading:

(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: BCPF030SA

It's the length of the file name that is the problem - here we are in v7.6.5 and the syntax still calls for an 8 character file name (I was using 9).

Yes

The 8 character boundary is especially for the younger once among us a problem.....

I haven't used this and I don't know if it will help you but there is a new function called DB_LOOKUP for 7.6.

Here is documentation from the SofNF for 7.6:

Retrieving a Value From a Fixed Format Sequential File in a TABLE Request
The following procedure creates a fixed format sequential file named GSALE from the
GGSALES data source. The fields in this file are PRODUCT (product description), CATEGORY
(product category), and PCD (product code). The file is sorted on the PCD field:
SET ASNAMES = ON
TABLE FILE GGSALES
SUM PRODUCT CATEGORY
BY PCD
ON TABLE HOLD AS GSALE FORMAT ALPHA
END

12. Reporting Language
The following Master File is generated as a result of the HOLD command:
FILENAME=GSALE, SUFFIX=FIX , $
SEGMENT=GSALE, SEGTYPE=S1, $
FIELDNAME=PCD, ALIAS=E01, USAGE=A04, ACTUAL=A04, $
FIELDNAME=PRODUCT, ALIAS=E02, USAGE=A16, ACTUAL=A16, $
FIELDNAME=CATEGORY, ALIAS=E03, USAGE=A11, ACTUAL=A11, $
The following TABLE request against the GGPRODS data source, sorts the report on the field
that matches the key field in the lookup file. It retrieves the value of the CATEGORY field
from the GSALE lookup file by matching on the product code and product description fields.
Note that the DEFINE FILE command is cleared at the end of the request:
DEFINE FILE GGPRODS
PCAT/A11 MISSING ON = DB_LOOKUP(GSALE, PRODUCT_ID, PCD,
PRODUCT_DESCRIPTION, PRODUCT, CATEGORY);
END
TABLE FILE GGPRODS
PRINT PRODUCT_DESCRIPTION PCAT
BY PRODUCT_ID
END
-RUN
DEFINE FILE GGPRODS CLEAR
END

There is more stuff there before and after this example.

I know, I know, I've been reading manuals again.

the DB_LOOKUP is a great function, but it does not always work well.
Small lookup tables do work.

Ginny,

Thanks for the suggestion. In this particular case, I'd rather not create masters for the files I want to use in the DECODE.

Regards,

Doug, thanks for your input. My problem was with accessing an external file in a DEFINE or COMPUTE. The solution is to use a DECODE and make sure the file name is 8 characters or less.

Cheers,

I know it is listed as solved but thought I would throw my 2 cents in. Of course it comes this is using a 'hold' ddname file and that this technique is limited based on the number of bytes being housed in that ddname, but I kept it out of the compute/define range...
TABLE FILE CAR PRINT COUNTRY CAR MODEL
WHERE COUNTRY NE 'W GERMANY'
ON TABLE HOLD AS S001
END
TABLE FILE CAR PRINT COUNTRY CAR MODEL
IF COUNTRY EQ (S001)
END