×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Grab one record with a condition

Grab one record with a condition

Grab one record with a condition

(OP)
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.

RE: Grab one record with a condition

TABLE FILE filename
PRINT *
WHERE STATUS EQ 'D'

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

RE: Grab one record with a condition

(OP)
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.

RE: Grab one record with a condition

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.

RE: Grab one record with a condition

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.

RE: Grab one record with a condition

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

RE: Grab one record with a condition

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

RE: Grab one record with a condition

(OP)
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.

RE: Grab one record with a condition

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close