-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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'
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|