Oracle SQL Query help

SELECT
    *
FROM orders
INNER JOIN (
            SELECT
                Order_No
            FROM transactions
            GROUP BY
                Order_No
            HAVING MIN(SubitemStatus) = MAX(SubitemStatus)
            AND MAX(SubitemStatus) = 'Sent'
        ) all_sent ON orders.Order_No = all_sent.Order_No
;

Select allOpen in new window

Using a GROUP BY and HAVING clause will allow you to find only those where all are sent.

You haven't specified what the "expected result" should look like so I have just assumed you want rows from the orders table.

details:

    CREATE TABLE Orders
        (ORDERNO int, ITEMNO int, ITEMSTATUS varchar2(10))
    ;
    
    INSERT ALL 
        INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
             VALUES (1234, 56566, 'Sent')
        INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
             VALUES (1234, 44545, 'Hold')
        INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
             VALUES (1234, 67888, 'Processing')
        INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
             VALUES (4567, 65656, 'Sent')
        INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
             VALUES (4567, 45466, 'Hold')
        INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
             VALUES (4566, 34566, 'Hold')
    SELECT * FROM dual
    ;
    
    CREATE TABLE Transactions
        (ORDERNO int, ITEMNO int, SUBITEMNO int, SUBITEMSTATUS varchar2(10))
    ;
    
    INSERT ALL 
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (1234, 56566, 5656601, 'Sent')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (1234, 56566, 5656602, 'Sent')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (1234, 44545, 4454501, 'Sent')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (1234, 44545, 4454502, 'Sent')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (1234, 67888, 6788801, 'Sent')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (4567, 65656, 6565601, 'Hold')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (4567, 45466, 4546601, 'Sent')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (4567, 45466, 4546602, 'Processing')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (4566, 34566, 3456601, 'Sent')
        INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
             VALUES (4566, 34566, 3456602, 'Sent')
    SELECT * FROM dual
    ;
    
**Query 1**:

    SELECT
        *
    FROM orders
    INNER JOIN (
                SELECT
                    OrderNo
                FROM transactions
                GROUP BY
                    OrderNo
                HAVING MIN(SubitemStatus) = MAX(SubitemStatus)
                AND MAX(SubitemStatus) = 'Sent'
            ) all_sent ON orders.OrderNo = all_sent.OrderNo

**[Results][2]**:
    | ORDERNO | ITEMNO | ITEMSTATUS | ORDERNO |
    |---------|--------|------------|---------|
    |    1234 |  67888 | Processing |    1234 |
    |    1234 |  44545 |       Hold |    1234 |
    |    1234 |  56566 |       Sent |    1234 |
    |    4566 |  34566 |       Hold |    4566 |

  [1]: http://sqlfiddle.com/#!4/f3b1b/2
  [2]: http://sqlfiddle.com/#!4/f3b1b/2/0

Select allOpen in new window

Or in traditional Oracle SQL syntax:
select * from orders o
where (o.order_no, o.itemno) in (select t.order_no,t. item_no, max(t.subitemstatus)
  from transaction_table t
  where t.subitemstatus = 'Sent'
  group by t.order_no,t. item_no);

If your Oracle database version is 11 or higher, you can use either ANSI-standard or traditional Oracle join syntax.  I would suggest trying them both to see if one approach or the other works faster in your system.

Thanks Paul. Query in not working as it should be. It's showing sent as well as hold records. Can you please check once?


Markgeer: I tried you query too. Throwing this error.
ORA 00913 Too many values.

Please help. What should be done?

This is what my previous query returns the following:

| ORDERNO | ITEMNO | ITEMSTATUS | ORDERNO |
|---------|--------|------------|---------|
|    1234 |  56566 |       Sent |    1234 |
|    1234 |  44545 |       Hold |    1234 |
|    1234 |  67888 | Processing |    1234 |
|    4566 |  34566 |       Hold |    4566 |

Select allOpen in new window

And that result meets this requirement:
               "Order 4567 should not be returned as one of the record Status is 'Hold'  and 'processing'.  "

but:
          >>" Query in not working as it should be. "

The trouble is you haven't really shown us what the correct result should be.

Please, using only the sample data you already provided, exactly what output are you expecting from the query?

Hi Maxwell,
Sorry, I apologize. The above result set is good. I missed some important info. In Transaction table we can have records with other status as well.  Please see below.

Transaction Table:
Order No, Item No, Sub Item no, SubitemStatus
1234 ,        56566,  5656601, Sent
1234 ,        56566,  5656602, Sent
1234 ,         44545,  4454501, Sent
1234 ,         44545,   4454502, Sent
A1234,          67888,  6788801, Hold

4567 , 65656, 6565601 Hold
4567 , 45466, 4546601 Sent
4567 , 45466, 4546602 Processing

4566,  34566, 3456601 Sent
A4566,  34566, 3456602 Processing

In the above table I have added A as a prefix in Order no 1234 and Order no 4566.

The result set still should be the same...
| ORDERNO | ITEMNO | ITEMSTATUS | ORDERNO |
|---------|--------|------------|---------|
|    1234 |  56566 |       Sent |    1234 |
|    1234 |  44545 |       Hold |    1234 |
|    1234 |  67888 | Processing |    1234 |
|    4566 |  34566 |       Hold |    4566 |

I do not currently see how that added data make any difference to outcome.

1234 transactions are all = 'sent', so order 1234 is included

4566 transactions are all ='sent', so order 4566 is included

orders A1234, A4566 and 4567 do not have all transactions as 'sent', so they are excluded.

------------ BUT
Are you also trying to indicate that order 'A1234' should in some way be linked to order '1234'?
 and that order 'A4566' is related to '4566'?

If this is true then NONE of the orders have ALL transactions = 'sent' and therefore NONE would be returned.

So. bottom line, I am confused by the new information.

Sorry, Max. Made a mistake again.. I apologize. Please see the change now. I expect the same result set as before.  In transaction table Item No. can be null and Sub Item no can start with prefix A. This kind of record should be excluded. Again, i am sorry for the earlier confusion.

Transaction Table:
Order No, Item No, Sub Item no, SubitemStatus
1234 ,        56566,  5656601, Sent
1234 ,        56566,  5656602, Sent
1234 ,         44545,  4454501, Sent
1234 ,         44545,   4454502, Sent
1234,           Null,    A6788801, Hold
1234,           Null,    A4566866, Hold

4567 , 65656, 6565601 Hold
4567 , 45466, 4546601 Sent
4567 , 45466, 4546602 Processing

4566,  34566, 3456601 Sent
4566,  Null,  A3456602 Processing


==================
| ORDERNO | ITEMNO | ITEMSTATUS | ORDERNO |
|---------|--------|------------|---------|
|    1234 |  56566 |       Sent |    1234 |
|    1234 |  44545 |       Hold |    1234 |
|    1234 |  67888 | Processing |    1234 |
|    4566 |  34566 |       Hold |    4566 |

then exclude them using a where clause ....

SELECT
    *
FROM orders
INNER JOIN (
            SELECT
                Order_No
            FROM transactions
            WHERE Item_No IS NOT NULL
            GROUP BY
                Order_No
            HAVING MIN(SubitemStatus) = MAX(SubitemStatus)
            AND MAX(SubitemStatus) = 'Sent'
        ) all_sent ON orders.Order_No = all_sent.Order_No
;

Select allOpen in new window

 WHERE Item_No IS NOT NULL appears to cover both conditions, if not add

AND NOT Item_No LIKE 'A%'

but don't add it if is not needed

Or, to fix up my earlier suggestion add one word, like this:

select * from orders o
 where (o.order_no, o.itemno, 'Sent') in (select t.order_no,t. item_no, max(t.subitemstatus)
   from transaction_table t
   where t.subitemstatus = 'Sent'
   group by t.order_no,t. item_no);

Max,
Thanks it works, but the only problem i am seeing is. Its pulling all the records from Orders table where ItemStatus is Sent, Processing, Hold. I don't want to see the orders for which all the ItemNo Status is Sent and all Transactions Status for respective order no are Sent.  See the Sample below. 4355 should not be displayed.


Order table:
OrderNo, ItemNo, ItemStatus
4355 ,  35643,  Sent
4355 ,   35643,  Sent
4355 ,    35643,  Sent

Transaction Table:
Order No, Item No, Sub Item no, SubitemStatus
4355 ,  35643,  9956601, Sent
4355 ,   35643,  9956602, Sent
4355 ,    35643,  9954501, Sent

SELECT
     ORDERNO, ITEMNO, ITEMSTATUS
FROM (
      SELECT
            o.ORDERNO, o.ITEMNO, o.ITEMSTATUS
          , MIN(o.ITEMSTATUS) OVER(PARTITION BY o.ORDERNO) AS MINSTATUS
          , MAX(o.ITEMSTATUS) OVER(PARTITION BY o.ORDERNO) AS MAXSTATUS
      FROM orders o
      INNER JOIN (
                  SELECT
                      OrderNo
                  FROM transactions
                  GROUP BY
                      OrderNo
                  HAVING MIN(SubitemStatus) = MAX(SubitemStatus)
                  AND MAX(SubitemStatus) = 'Sent'
              ) all_sent ON o.OrderNo = all_sent.OrderNo
      ) ALL_O
WHERE NOT (MINSTATUS = 'Sent' AND MAXSTATUS = 'Sent')
;

Select allOpen in new window

are all the requirements known now?

Hi Max, Thanks.
When i run the above query, I do not get the below results.

Is it accounting for all the rules i have given above. Like one of them was (WHERE Item_No IS NOT NULL) in Transactions table.


| ORDERNO | ITEMNO | ITEMSTATUS | ORDERNO |
|---------|--------|------------|---------|
|    1234 |  56566 |       Sent |    1234 |
|    1234 |  44545 |       Hold |    1234 |
|    1234 |  67888 | Processing |    1234 |
|    4566 |  34566 |       Hold |    4566 |

I'm sorry is that result showing above NOT what you get? if so, what DO you get instead?

Sorry, for the delayed response.. I was not able to login to the forum lately. Thanks for your help.