query request

hello

Can any one help me in writing this query please

we have order history table where I am trying to get the next row when an order got reprocessed

order history table

orderid-orderstageid-processdate-orderinfo
1234-2-05-jun-07 11:12:34AM-reporcessed
1234-2-05-jun-07 11:12:34AM-missing part

7723-5-06-jun-07 2:02:59AM - reprocessed
7723-5-06-jun-07 2:02:59AM -wrong part

I am trying to get the row after the reprocess. they will have same orderstage_id and process date

can anyone help me in getting a query

Thanks

Looking at the info you have provided I'm not sure if it's doable.

Please post actual desc of your table, include comment for each line describing content, list of possible values when finite.

Check this query.


select orderstage_id,process_date from orderhistory
where orderstage_id in (select orderstage_id from orderhistory
where orderinfo='reprocessed') and orderinfo!='reprocessed'

Quote:

---

Originally Posted by malay_biswal

Check this query.

select orderstage_id,process_date from orderhistory
where orderstage_id in (select orderstage_id from orderhistory
where orderinfo='reprocessed') and orderinfo!='reprocessed'

---

Your query doesn't take into consideration neither cardinality nor order. You cannot just assume there are two rows per orderstage_id.

How do you know returned row/rows is/are the next to "reprocessed" row?
1- Your query has the potential to return multiple rows
2- Your query has the potential to return rows before/after "reprocessed" row

Better to wait for OP to clarify scenario... then try to help him/her.

He/she's the person who knows what he wants. Whatever info i've now i tried to put them together and the other logic he/she can take care of. I would appreciate if you do not advice, when to help others.

You are not helping at all

thank you but the query you gave is giving me multiple rows except the one after the reprocessed row

appreciate your help

Tell us please what u mean by "next row"?
How are they ordered so that we know what is "next"
Support malay_biswal, from what u have posted seems that either u have just 2 rows per ID or u do not know what u need

Regards

sorry if i was not clear earlier ..in the history table i have will have rows for every stage like every order goes through 10 different stages from ordering to complete.

i believe the query works when we have only 2 rows in the table but we have more.
I am looking for a query next to reprocessed row.it's always the next row and some one can reprocess an order any time.

Thanks

Looking that this scenario it would be better for your to migrate this table to a sequential -tape based- file; have you seen those at the museum?

Now, seriously...

... can an order be reprocessed twice?
... is your processdate a DATE datatype column?

1.yes it can be reprocessed multiple times but rarely happens
2.yes it's date datetype

Assuming you have no more than one reprocess per orderid/orderstageid query below should work. If you can have more than one reprocess per orderid/orderstageid additional logic is needed to sort out different reprocess items.

Code:

---

select  a.orderid,
        a.orderstageid,
        a.processdate,
        a.orderinfo
from    order_history a
where  a.orderid || a.orderstageid = (select  b.orderid || b.orderstageid
                                      from    order_history b
                                      where    a.orderid || a.orderstageid = b.orderid || b.orderstageid
                                      and      b.orderinfo = 'reprocessed')
  and  a.orderinfo  <> 'reprocessed'
  and  a.processdate >= (select  c.processdate
                          from    order_history c
                          where    a.orderid || a.orderstageid = c.orderid || c.orderstageid
                          and      c.orderinfo = 'reprocessed')
;

---

Hope this helps.