query request

[prodadmin]

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

[PAVB]

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.

[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'

[PAVB]

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.

[malay_biswal]

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.

[PAVB]

You are not helping at all

[prodadmin]

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

appreciate your help

[Bore]

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

[prodadmin]

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

[PAVB]

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?