Results 1 to 1 of 1

Thread: Query help.

  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Query help.

    DB2 on AS/400 I dont know the version but the system is 2 years old.

    Here is my question, I will bore you with a sob story about how my life is ruined and then I will ask for your help. My question is how do I complete this query to get the needed result?

    Code:
    SELECT RDSTAT, \"RDORD#\" FROM ORPRDETL WHERE RDSTAT = 200 AND RDENDX = '2006-01-31' ORDER BY RDSTAT
    Very basic query it gets me the first set of my results which is every invoice that has left area 200 on 1/31/2006, now the next piece is need, and have been struggling with it for awhile now is how can I get (subselect or something) the invoices current location. The problem i am facing is that this table, all the information is in one table, contains the invoice and all the areas that the invoice has and will visit, the only difference is that if the invoice is not in that area or hasnt left an area the deault date and time are set, date=1940-01-01, time=00:00:00, also two fields are set at 0. However I am unclear on how to take this information and use it on the above query.

    Here is an example of the output of an invoice that is at area 700 after leaving area 200 on 2/15/2006.

    Code:
    657926 E 1 1 10 100 2006-02-06 11:00:00 71 2006-02-06 11:00:00 71 0 1.00 C 1940-01-01 
    657926 E 2 1 20 150 2006-02-03 12:21:00 5 2006-02-03 12:21:00 5 0 0 C 1940-01-01 
    657926 E 3 1 30 200 2006-02-06 11:00:00 71 2006-02-15 09:43:00 71 0 8.00 C 1940-01-01 
    657926 E 4 1 40 300 2006-02-22 15:40:00 103 2006-02-23 16:37:00 10 0 2.00 C 1940-01-01 
    657926 E 5 1 50 400 2006-02-28 23:01:00 26 2006-02-28 23:01:00 26 0 1.00 C 1940-01-01 
    657926 E 6 1 60 500 2006-03-02 13:46:00 12 2006-03-02 13:46:00 12 0 1.00 C 1940-01-01 
    657926 E 7 1 70 600 2006-03-02 16:50:00 67 2006-03-02 16:50:00 67 0 1.00 C 1940-01-01 
    657926 E 8 1 80 700 2006-03-03 07:08:00 5 1940-01-01 00:00:00 0 0 0 GAAS 1940-01-01 
    657926 E 9 1 90 800 1940-01-01 00:00:00 0 1940-01-01 00:00:00 0 0 0 GAAS 1940-01-01
    Can anyone help?
    Last edited by iceman42; 03-07-06 at 14:12.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •