Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Unanswered: PLSQL - Problem with mod / IF

    I filled up an Variable named gpal and i want to check if the Variable ist an Integer with the mod. But the Output gives out all the Entrys also the not Integer. Can you help me ? Here is my Script :


    DECLARE
    CURSOR c_pal IS
    select t3.id,t3.name,t1.artid_artnu12,t1.artid_pt,t1.best mng,t1.akehe,t1.gelmng,sum (t2.ahepal * t2.akehe) pal,sum (t1.gelmng / (t2.ahepal * t2.akehe))gpal
    from dinu_25 t1,posten t2,partner t3
    where t1.warenempf = t3.epin
    and t1.artid_artnu12=t2.artid_artnu12
    and t1.artid_mandid=t2.artid_mandid
    and t1.artid_pt = t2.artid_pt
    and t1.gelmng !=0
    and (t1.gelmng / (t2.ahepal * t2.akehe)) > 1
    group by t3.id,t3.name,t1.artid_artnu12,t1.artid_pt,t1.best mng,t1.akehe,t1.gelmng;

    v_pal c_pal%ROWTYPE;
    gpal number;

    BEGIN
    OPEN c_pal;
    LOOP
    FETCH c_pal INTO v_pal;
    EXIT WHEN c_pal%NOTFOUND;
    IF mod(gpal,(gpal*1))=0 THEN
    DBMS_OUTPUT.PUT_LINE ('Filiale :' || v_pal.id ||' '|| v_pal.name ||' '|| ' Paletten : ' ||substr (v_pal.gpal,1,5));
    END IF;

    END LOOP;
    CLOSE c_pal;
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You seem to believe that x*1 is not equal to x, which clearly isn't true! :-
    Code:
    SQL> select x, x*1, mod(x,x*1) from
      2  (select rownum/4 x from emp);
    
             X        X*1 MOD(X,X*1)
    ---------- ---------- ----------
           .25        .25          0
            .5         .5          0
           .75        .75          0
             1          1          0
          1.25       1.25          0
           1.5        1.5          0
          1.75       1.75          0
             2          2          0
          2.25       2.25          0
           2.5        2.5          0
          2.75       2.75          0
             3          3          0
          3.25       3.25          0
           3.5        3.5          0
    Try:

    IF gpal = TRUNC(gpal) THEN ...

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    btw, any particular reason for writing that cursor loop the hard way? Why not simply:

    Code:
    FOR v_pal IN c_pal LOOP
    	IF mod(gpal,(gpal*1))=0 THEN
    		DBMS_OUTPUT.PUT_LINE(...);
    	END IF;
    END LOOP;

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps it is an older Oracle version? I know for sure that 7.1 (still in production on some servers in my company) doesn't support cursor FOR loops.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Littlefoot
    Perhaps it is an older Oracle version? I know for sure that 7.1 (still in production on some servers in my company) doesn't support cursor FOR loops.
    Really? I have worked with Oracle since about 6.0 and I don't recall there ever being a time when there was no cursor FOR loop. Maybe I'm just getting senile?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm afraid it is me who is spreading false information ... Oracle 7 is down on my site so I asked a friend to run a simple script containing the cursor FOR loop. Guess what? It compiled successfully. Now I'm completely confused; I was sure cursor FOR loops were available from Oracle 8 ...

    I sincerely apologize for such a misleading information!

Posting Permissions

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