[RikoOnWeb]

Hi, I have a table that contains steps of a work order processing. Normally, each step should have it's closed flag set to Y as it gets finished. Sometimes this doesn't happen, so there are steps left open with lower sequence than steps that are already closed. I would need to select all these steps (incorrectly left opened).

For simplicity, the table JOB_OPERATIONS have columns JOB, SUFFIX, SEQ and FLAG_CLOSED. Every work order has it's unique job and suffix number combination. Each step has it's ascending sequence number in SEQ. Step can be either closed (Y in FLAG_CLOSED) or opened (FLAG_CLOSED empty).

All I could figure out is using two views and join them in a query but Pervasive 8.6 doesn't support grouped views (ODBC Error: SQLSTATE = S1000, Native error code = 0 Grouped view joins are not supported.) I am not sure if upgrade is an option.

Is there any other way how to get the records I need?

Thanks, I will appreciate any help.

[mirtheil]

You might be able to issue the two queries joined without having the views. If that doesn't work, post the queries and we'll take a look at it to see if there's something we can suggest.

[RikoOnWeb]

To be honest, I am not sure how to join those queries in Pervasive 8.6. The queries are:

SELECT job, suffix, MIN(seq) as min_closed FROM job_operations WHERE flag_closed = 'Y' AND lmo = 'L' GROUP BY job, suffix

and

SELECT job, suffix, MIN(seq) as min_opened FROM job_operations WHERE flag_closed <> 'Y' and lmo = 'L' GROUP BY job, suffix

Essentially, I would need records where min_opened is less than min_closed.

Again, thank you for your help.