Hi,
The datamart for our advancement division is a multi-segment focus database created using MODIFY. We are having a problem when doing a WHERE test against multiple segments. For example, we have this statement:
WHERE STAF_CON_CD1 EQ 'DNB' OR SPC_PRP_TYPE EQ 'DNB'
In the .mas file for our database, the field STAF_CON_CD1 is in a segment called CONS_02 with segtype=U, and the parent=root. The field SPC_PRP_TYPE is in a segment called SPECPURP with segtype=S2, also with parent=root. Not all constituents have an entry in either the CONS_02 or the SPECPURP segment. The above WHERE statement seems to eliminate those constituents that have a STAF_CON_CD1 EQ 'DNB' but that have no records at all in the SPECPURP segment.
Any ideas how to get around this problem? I can't use SET ALL=PASS or the .ALL operator because then I seem to get contituents that are missing either segment. I need my answer set to be constituents that 1) have STAF_CON_CD1 EQ 'DNB' (and SPC_PRP_TYPE can either be 'DNB' or the SPECPURP segment can be missing) or 2) have SPC_PRP_TYPE EQ 'DNB' (with STAF_CON_CD1 EQ 'DNB' or the CONS_02 segment can be missing). To summarize what I am trying to do... if I run a request just with STAF_CON_CD1 EQ 'DNB' and I get 100 records, and then I run a request just with SPC_PRP_TYPE EQ 'DNB' and get 50 records then I am expecting to get 150 records (100+50) if I run a request with WHERE STAF_CON_CD1 EQ 'DNB' OR 'SPC_PRP_TYPE EQ 'DNB'.

Any ideas, suggestions would be greatly appreciated.

Thanks!

(WF 7.6.5/7.6.10 running on Windows)

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

I think you have your login in your post.

I think you have your answer in your post. (Bad case of aphasia)


Perhaps you could use WHERE TOTAL and SET ALL=PASS.

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

Hi,
What do you mean by "I think you have your login in your post"?? My post does not contain any usernames or passwords...

You could use MATCH FILE for this. The first pass will select the appropriate records from the CONS_02 segment and the second pass will select the records from the SPECPURP segment.

MATCH FILE XXXXX
PRINT STAF_CON_CD1
BY KEY_IN_PARENT
WHERE STAF_CON_CD1 EQ 'DNB'
RUN
FILE XXXXX
PRINT SPC_PRP_TYPE
BY KEY_IN_PARENT
WHERE SPC_PRP_TYPE EQ 'DNB'
AFTER MATCH HOLD OLD-AND-NEW
END

SET ALL=ON

TABLE FILE HOLD
PRINT STAF_CON_CD1 SPC_PRP_TYPE
BY KEY_IN_PARENT
END

Hi,
Thanks for the response.
Yes, I know MATCH will work but unfortunately my users write reports against our datamart using a menu driven system (written long before I arrived on the scene) that allows them to construct IF, WHERE, and BY statements, create headings and subtotals, and specify an output format. There is no way for them to create a MATCH request. I was hoping there was a SET flag or something that I was missing that would allow me to work around the problem.

quote:

Originally posted by George Patton:
You could use MATCH FILE for this. The first pass will select the appropriate records from the CONS_02 segment and the second pass will select the records from the SPECPURP segment.

MATCH FILE XXXXX
PRINT STAF_CON_CD1
BY KEY_IN_PARENT
WHERE STAF_CON_CD1 EQ 'DNB'
RUN
FILE XXXXX
PRINT SPC_PRP_TYPE
BY KEY_IN_PARENT
WHERE SPC_PRP_TYPE EQ 'DNB'
AFTER MATCH HOLD OLD-AND-NEW
END

SET ALL=ON

TABLE FILE HOLD
PRINT STAF_CON_CD1 SPC_PRP_TYPE
BY KEY_IN_PARENT
END

OOPS - I took a shower after writing this and as usual the brain started to work...

I should have written:

AFTER MATCH HOLD OLD-OR-NEW

Leo

Maybe you can try to explain the end-users that the webfocus world has more to offer then they use.
Show them some new options, maybe the can use developer studio or infoassist for there needs.