Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Unanswered: While loop (DB2 in AS/400)

    Hello guys,

    I'm new to the AS/400 SQL programming. That's why i would need a little help from you on this case.

    Here's my problem :

    I want to extract the time of connexion of forklift drivers. They move pallets from one place to another one. So i've got the hour when the move began, and the hour when the move has finished.

    id_movement | Person_name | Start_hour | End_hour
    1 JFWD 71213 71435
    2 JFWD 71512 71632
    3 JFWD 91234 91434
    4 JFWD 91623 91712

    But there's an exception. If there's more than 10 minutes between two Start_hour, i do not have to count it. So what i would like to do (algorithmically speaking) is :


    With t(i) as (values('0')), n(p) as (values('0'))-- only thing that works in my environment, all the lines ending with ; do not work

    while i < 100 000 Do

    Select

    case when

    (select Start_hour -- this 2 passages = Ans
    from table, t, n
    where id_movement =i+1)
    -
    (select Start_hour
    from table, t, n
    where id_movement =i)

    > 600

    Then p = p + Ans -- amount of time to not count
    Else p = p + 0

    End,

    (max(End_hour) - min(Start_hour) - p)

    from table

    I've tried some examples available in the reference manual (SC23-5862) but i do not understand them.

    Thanks for your help,

    Willie_12

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This is an example of using "while" in db2udb sql on as400:
    https://publib.boulder.ibm.com/infoc...twhilestmt.htm

    And since you will need to do data/time arithmetic here are some examples:
    https://publib.boulder.ibm.com/infoc...arithmetic.htm

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some questions.
    (1) What are the format of Start_hour and End_hour?

    (2) Your psudo code(?) was difficult to understand for me.
    Would you provide more sample data and expected results from the sample data?
    For example:
    (2-1) another Person_name of who doesn't include two pair of data which "more than 10 minutes between two Start_hour".
    (2-2) third Person_name of who does include more than two pair of data which "more than 10 minutes between two Start_hour".
    so on...

    (3) Are id_movement always consecutive and start from 1?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thoght that if you want (a)one result or (b)a result for each row, from the sample data,
    Quote Originally Posted by Willie_12 View Post
    id_movement | Person_name | Start_hour | End_hour
    1 JFWD 71213 71435
    2 JFWD 71512 71632
    3 JFWD 91234 91434
    4 JFWD 91623 91712
    no procedual programming might be not necessary.
    (i.e. one SQL SELECT statement(it might include sub query, so on...) might be enough.)


    So, (at least) I hope you to publish the expected result(s) from the data, like...
    (a) One result for each Person_name.
    Code:
    Person_name The result
    ----------- ----------
    JFWD        xxxx
    or

    (b) A result for each row.
    Code:
    id_movement | Person_name | Start_hour | End_hour | result
    1             JFWD          71213        71435      xxx
    2             JFWD          71512        71632      xxx
    3             JFWD          91234        91434      xxx
    4             JFWD          91623        91712      xxx
    or

    (c) Oher results.
    ...

Posting Permissions

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