CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloper
Spec
Spec

Reputation: 25

How to get only max value from variable in nested for loop

i need help with this code? Result is :

but i espected like this :

max value from loop of user

 DECLARE

v_z VARCHAR2(50);
max_s  VARCHAR2(50);

BEGIN

max_s := -9999999999999;

FOR i     IN
(SELECT user,
  in,
  out 
  FROM emp)

LOOP
FOR j IN
(SELECT user,
  in,
  out
FROM emp
AND user = i.user
)
LOOP
  IF i.in < j.in AND i.out < j.out AND i.out > j.in AND i.out < j.out AND i.user = j.user 

THEN
    v_z  := ROUND((j.out-i.in),4)*1440 ;

    FOR r                         IN v_z..v_z
    LOOP
      IF r > max_s THEN
        dbms_output.put_line (i.user || ' ' ||r);
      END IF;
    END LOOP;

  END IF;
END LOOP;
END LOOP;
END;

table example:

  user    in     out
  steve  7:00    9:30
  steve  8:00   10:00
  steve  9:00   11:00

And i need result 11:00 - 7:00 for steve not 10 - 7:00 or 11:00 - 8:00 who also in if condition is true, i want max value for this user

Upvotes: 0

Views: 478

Answers (2)

M Moore
M Moore

Reputation: 18

I would still suggest you're best solution is to use SQL to pull the values rather than loops. Looks like you want the max(out) - min(in) grouped by user.

Off the top of my head, I'd suggest something like: select user, min(in), max(out) from emp group by user;

If you're looking for it do the math: select user, out-in from (select user, min(in) as in, max(out) as out from emp group by user);

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30635

this can be easily done with single select statement. Clearly no benefit of using PL/SQL and it is definitely slower.

select name, max(num) from tbl
group by name

Upvotes: 1

Related Questions