Oracle SQL Query help

Experts,
Need some help with query.

Order table:
OrderNo, ItemNo, ItemStatus
1234 , 56566,  Sent
1234,  44545,  Hold
1234,  67888,  Processing

4567 , 65656, Sent
4567 , 45466, Hold

4566,  34566, Hold

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,          67888,  6788801, Sent

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

4566,  34566, 3456601 Sent
4566,  34566, 3456602 Sent

Basically, i want to return only the records for the SubitemsStatus= "Sent" each record for the Order should be Sent status, if one record is 'sent' and other is 'Processing' 'hold' etc. than exclude the entire order no. For the above table. My result set should return..
 Order No, 1234 and 4566. Order 4567 should not be returned as one of the record Status is 'Hold'  and 'processing'.  

Thanks in advance.
techlearnAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
;

Open 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

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
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.
techlearnAuthor Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

techlearnAuthor Commented:
Please help. What should be done?
PortletPaulEE Topic AdvisorCommented:
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 |

Open 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?
techlearnAuthor Commented:
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 |
PortletPaulEE Topic AdvisorCommented:
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.
techlearnAuthor Commented:
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 |
PortletPaulEE Topic AdvisorCommented:
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
;

Open 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
Mark GeerlingsDatabase AdministratorCommented:
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);
techlearnAuthor Commented:
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
PortletPaulEE Topic AdvisorCommented:
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')
;

Open in new window


are all the requirements known now?
techlearnAuthor Commented:
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 |
PortletPaulEE Topic AdvisorCommented:
I'm sorry is that result showing above NOT what you get? if so, what DO you get instead?
PortletPaulEE Topic AdvisorCommented:
This is what I get as a result:
| ORDERNO | ITEMNO | ITEMSTATUS |
|---------|--------|------------|
|    1234 |  56566 |       Sent |
|    1234 |  44545 |       Hold |
|    1234 |  67888 | Processing |
|    4566 |  34566 |       Hold |

Open in new window


from this query:
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')
;

Open in new window


From this data:
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
;
INSERT ALL 
    INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
         VALUES (4355, 35643, 'Sent')
    INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
         VALUES (4355, 35643, 'Sent')
    INTO Orders (ORDERNO, ITEMNO, ITEMSTATUS)
         VALUES (4355, 35643, 'Sent')
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
;
INSERT ALL 
    INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
         VALUES (4355, 35643, 9956601, 'Sent')
    INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
         VALUES (4355, 35643, 9956602, 'Sent')
    INTO Transactions (ORDERNO, ITEMNO, SUBITEMNO, SUBITEMSTATUS)
         VALUES (4355, 35643, 9954501, 'Sent')
SELECT * FROM dual
;

Open in new window


See it working here: http://sqlfiddle.com/#!4/89fe2/1

adding the where clause (line 13):
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
                  WHERE ItemNo IS NOT NULL --<< add this
                  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')
;

Open in new window


see that: http://sqlfiddle.com/#!4/89fe2/3
(same result for that data)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
techlearnAuthor Commented:
Sorry, for the delayed response.. I was not able to login to the forum lately. Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.