I'm trying to get something to work in v7.7.02 that used to work in 7.6.1
I had used a subquery in 1st step to use as criteria in 2nd step:
Step on was to get a list of possible values
TABLE FILE BRIEF
PRINT COMM
BY CAMP
WHERE CAMP CONTAINS 'NA0009';
ON TABLE HOLD AS FACF
END
Step 2 was to use this HOLD file as WHERE ...EQ FACF

TABLE FILE CHANNEL
PRINT
COMM
CAMP
WHERE CAMP EQ (FACF)
END

That no longer seems to work in 7.7.02. So I'm trying to follow some other post examples like this:
TABLE FILE BRIEF
BY CAMP
WHERE CAMP CONTAINS 'NA0009';
ON TABLE SAVE AS FACF FORMAT ALPHA
END
TABLE FILE CHANNEL
PRINT
COMM
CAMP
WHERE CAMP IN FILE FACF;
END

When I look at trace, the SQL criteria expands possible values in 1st step. In the 1st step, I know that the only value is 'NA0009MC01'. But the SQL trace shows value of '000010NA0009MC01'
Question is, why are those extra characters 000010 in the SQL WHERE?

thanks

BobV

This message has been edited. Last edited by: BobV,

Bob, 2 things:

1) If your FACF hold is to contain a list of values you will probe against CHANNEL.CAMP, the the HOLD file should only contain the values for one field ... but I can see in your TABLE FILE BRIEF statement that you are saving values for 2 fields: CAMP and COMM into the HOLD file. This may impact the results later when you try to WHERE CAMP EQ (FACF) ... only one field should be in the HOLD file.

2) The 000010 seems to be the length of 'NA0009MC01'. Is BRIEF.CAMP an AnV field? That's the way they behave. If you want 'NA0009MC01' only you will have to convert it to a "regular" An field before saving it to the HOLD file.

Try this;

TABLE FILE BRIEF
SUM
COMPUTE A_CAMP/A30 = CAMP;
BY CAMP NOPRINT
ON TABLE HOLD AS FACF FORMAT ALPHA
END

and then test if your WHERE .. (FACF) in the coming statements work now.

The syntax for a WHERE in a file is:

WHERE column-name IN FILE file-name

Here's an example:

TABLE FILE CAR
PRINT
COUNTRY
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE'
ON TABLE HOLD AS SEL1 FORMAT ALPHA
END

TABLE FILE CAR
PRINT *
WHERE COUNTRY IN FILE SEL1
END

The syntax you describe is for IF:

IF column-name EQ (file-name)

Thanks njsden. Changing format to A30 worked. Yes, CAMP field is an A30v

quote:

Originally posted by njsden:
Bob, 2 things:

1) If your FACF hold is to contain a list of values you will probe against CHANNEL.CAMP, the the HOLD file should only contain the values for one field ... but I can see in your TABLE FILE BRIEF statement that you are saving values for 2 fields: CAMP and COMM into the HOLD file. This may impact the results later when you try to WHERE CAMP EQ (FACF) ... only one field should be in the HOLD file.

2) The 000010 seems to be the length of 'NA0009MC01'. Is BRIEF.CAMP an AnV field? That's the way they behave. If you want 'NA0009MC01' only you will have to convert it to a "regular" An field before saving it to the HOLD file.

Try this;

TABLE FILE BRIEF
SUM
COMPUTE A_CAMP/A30 = CAMP;
BY CAMP NOPRINT
ON TABLE HOLD AS FACF FORMAT ALPHA
END

and then test if your WHERE .. (FACF) in the coming statements work now.