Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: Help with select query

    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.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Nov 2007
    Posts
    3

    Queries

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •