Thread: Loop

Hi,

I have a loop of the form:

    FOR rec IN SELECT code FROM staff WHERE shiftgroup = NEW.groupe ORDER
BY code LOOP
        WHILE sdate <= NEW.todate LOOP
            SELECT INTO starty,endy,nday resumetime,closetime,nextday FROM
shifts WHERE shift = NEW.shift;
            restime := sdate + starty;

            IF nday = true THEN
                clstime := sdate + interval '1 day' + endy;
            ELSE
                clstime := sdate + endy;
            END IF;

            INSERT INTO shiftsexp(id,staff,resumetime,closetime)
VALUES(NEW.id,rec.code,restime,clstime);
            sdate := sdate + interval '1 day';
        END LOOP;
    END LOOP;

Surprisingly, the outer loop (For .. Loop) does not loop while only
the WHILE ... Loop works.

Any hint would be appreciated.

Regards,
Chris.

On 12 Sep 2009, at 24:17, db.subscriptions@shepherdhill.biz wrote:

> Hi,
>
> I have a loop of the form:
>
>     FOR rec IN SELECT code FROM staff WHERE shiftgroup = NEW.groupe
> ORDER BY code LOOP
>         WHILE sdate <= NEW.todate LOOP
>             SELECT INTO starty,endy,nday resumetime,closetime,nextday FROM
> shifts WHERE shift = NEW.shift;
>             restime := sdate + starty;
>
>             IF nday = true THEN
>                 clstime := sdate + interval '1 day' + endy;
>             ELSE
>                 clstime := sdate + endy;
>             END IF;
>
>             INSERT INTO shiftsexp(id,staff,resumetime,closetime) VALUES
> (NEW.id,rec.code,restime,clstime);
>             sdate := sdate + interval '1 day';
>         END LOOP;
>     END LOOP;
>
> Surprisingly, the outer loop (For .. Loop) does not loop while only
> the WHILE ... Loop works.

What makes you think that? There's nothing in your code that uses the
record from your outer loop, so how do you know it doesn't loop? Did
you raise notices to check? Why do you even have that loop, it doesn't
appear to serve a purpose?

> Any hint would be appreciated.
>
> Regards,
> Chris.

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.


!DSPAM:737,4aab70c813781789017984!

On 12 Sep 2009, at 11:58, Alban Hertroys wrote:

> On 12 Sep 2009, at 24:17, db.subscriptions@shepherdhill.biz wrote:
>
>> Hi,
>>
>> I have a loop of the form:
>>
>>     FOR rec IN SELECT code FROM staff WHERE shiftgroup = NEW.groupe
>> ORDER BY code LOOP
>>         WHILE sdate <= NEW.todate LOOP
>>             SELECT INTO starty,endy,nday resumetime,closetime,nextday FROM
>> shifts WHERE shift = NEW.shift;
>>             restime := sdate + starty;
>>
>>             IF nday = true THEN
>>                 clstime := sdate + interval '1 day' + endy;
>>             ELSE
>>                 clstime := sdate + endy;
>>             END IF;
>>
>>             INSERT INTO shiftsexp(id,staff,resumetime,closetime) VALUES
>> (NEW.id,rec.code,restime,clstime);
>>             sdate := sdate + interval '1 day';
>>         END LOOP;
>>     END LOOP;
>>
>> Surprisingly, the outer loop (For .. Loop) does not loop while only
>> the WHILE ... Loop works.
>
> What makes you think that? There's nothing in your code that uses
> the record from your outer loop, so how do you know it doesn't loop?
> Did you raise notices to check? Why do you even have that loop, it
> doesn't appear to serve a purpose?


Ah wait, ENOCOFFEE... You do use the rec variable.

What you don't do is reset the sdate to the value it had at the start
of your outer loop, so you end up immediately meeting the condition of
the WHILE expressions' loop after the first iteration through your FOR
loop. So it loops through the outer loop, but after the first time
there's no work left to be done inside it.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4aab752a13787908612439!