query request
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: query request

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    Arrow

    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'
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  7. #7
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    thank you but the query you gave is giving me multiple rows except the one after the reprocessed row

    appreciate your help

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  9. #9
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    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

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  



Click Here to Expand Forum to Full Width