Grab one record with a condition

[H1004]

This is probably really easy, but yet I can't think of a way to rab this data.
Let's say you have TWO records with same order # and of course different uniqueID. However, one record as a status as deliver (D), and another record with a complete (Z) status. What I want to do is grab all records with a deliver status and not the completed one.
So what I did was
TABLE FILE filename
PRINT *
WHERE STATUS NE 'Z';
WHERE STATUS EQ 'D';
END

But that wouldn't work because it still has one record with status of D on there.
I can't think of a way to eliminate those order # that has both of this status.
I can't say
WHERE STATUS NE 'Z' AND 'D';
cause it would not show up those order # that has D and not Z.

 

[gizzy17]

TABLE FILE filename
PRINT *
WHERE STATUS EQ 'D'

will only give you the orders that have been delivered -- no 'complete' orders should be returned.

 

[H1004]

I think I might have confused you on my explanation...
TWO records
uniqueID order# status
aaa 122 D
bbb 122 Z
ccc 222 D
ddd 333 D

What I want my result to look like is
ccc
ddd

Hope this helps clarify what I'm look for.

 

[gizzy17]

Try this --

DEFINE FILE filename
BADREC/I4 = (STATUS NE 'D');
END
TABLE FILE filename
SUM BADREC
BY ORDER
PRINT STATUS
BY ORDER
BY UNIQUEID
WHERE TOTAL C1 EQ 0
END

This requires a lot of sorting if the file is large and may be better to split into two different request. However, if the file is not that big you might not even notice the difference too much.

 

[kiddpete]

This should do it:

DEFINE FILE filename
DUMMY/A1=' ';
END
MATCH FILE filename
PRINT *
BY ORDER#
WHERE STATUS EQ 'D'
RUN
FILE filename
PRINT DUMMY
BY ORDER#
WHERE STATUS EQ 'Z'
AFTER MATCH HOLD OLD-NOT-NEW
END

and the desired records should be in the HOLD file.

 

[H1004]

Matching works!!! But it takes way too long to process.
Thank you!!!

 

[Tewy]

Try this:

TABLE FILE filename
SUM
UNIQUEID
BY ORDER#
BY HIGHEST 1 STATUS
WHERE TOTAL STATUS NE 'Z';
END

As 'Z' is higher than 'D' it should return one line per order number, with 'Z' for status if the order number is complete. The Where Total is evaluated after the data has been returned to your pc (in comparison with a Where which is evaluated before the data is returned) so only at this stage does it exclude records with a 'Z'

Tewy

 

[H1004]

I tried this method, and it was slower than the match concept. It seems that when we do this kind of query in Webfocus, it's very very slow.
Another thing I probably should point out is that I'm grabbing more fields than what I put on here and more where clause. I need to figure a different way coz I don't think my user would want to wait more than 10 minutes for a report.
Thank you for your help.
This is the actual scenerio and volume:

JOIN CLEAR *
JOIN UNIQUEID IN TABLE1 TO ALL UNIQUEID IN TABLE2 AS JOIN0

TABLE FILE TABLE1
SUM
UNIQUEID
FIELD2
FIELD3
FIELD4
FIELD5
FIELD6
FIELD7
WRK_TYP_CDE
FIELD8
WRK_STS_CDE
FIELD9
BY ORDER#
BY HIGHEST 1 WRK_STS_CDE
WHERE TOTAL ( WRK_TYP_CDE EQ '7200') OR (WRK_TYP_CDE EQ '71$$');
WHERE TOTAL WRK_STS_CDE NE 'Z';
WHERE TOTAL WRK_STS_CDE NE 'N';
WHERE TOTAL REC_ADD_DTM GE '200300000000';
-*ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX FIELD2
END
-EXIT

This codes alone is taking more than 10 minutes to generate.

 

[gizzy17]

where total will retrieve every row and then remove them just before providing the report. If you take the total option off of WRK_TYP_CDE and REC_ADD_DTM, you will probably have much better results.