var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: While loop (DB2 in AS/400)
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 Start_hour -- this 2 passages = Ans
from table, t, n
where id_movement =i+1)
from table, t, n
where id_movement =i)
Then p = p + Ans -- amount of time to not count
Else p = p + 0
(max(End_hour) - min(Start_hour) - p)
I've tried some examples available in the reference manual (SC23-5862) but i do not understand them.
Thanks for your help,
This is an example of using "while" in db2udb sql on as400:
And since you will need to do data/time arithmetic here are some examples:
(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?
(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".
(3) Are id_movement always consecutive and start from 1?
I thoght that if you want (a)one result or (b)a result for each row, from the sample data,
no procedual programming might be not necessary.
Originally Posted by
(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.
JFWD The result xxxx
(b) A result for each row.
id_movement | Person_name | Start_hour | End_hour |
1 JFWD 71213 71435 result
2 JFWD 71512 71632 xxx
3 JFWD 91234 91434 xxx
4 JFWD 91623 91712 xxx xxx
(c) Oher results.