Home » SQL & PL/SQL » SQL & PL/SQL » calculate the max and sum value by weird way !!! (Oracle 10g , windows XP SP2)
calculate the max and sum value by weird way !!! Fri, 22 August 2008 18:19
 Hany Freedom Messages: 256Registered: May 2007 Location: Egypt Senior Member
this code calculate the sum value without sum clause :-

```DECLARE
x NUMBER;
y NUMBER := 0;

CURSOR cur_sal
IS
SELECT sal
FROM emp;
BEGIN
OPEN cur_sal;

LOOP
FETCH cur_sal
INTO x;

EXIT WHEN cur_sal%NOTFOUND;
y := y + x;
END LOOP;

CLOSE cur_sal;

DBMS_OUTPUT.put_line ('sum sal without using sum function = ' || y);
END;
/```

-------------------------

and this code calculate the max value without max clause :-

```DECLARE
x NUMBER;
y NUMBER := 0;

CURSOR cur_sal
IS
SELECT sal
FROM emp;
BEGIN
OPEN cur_sal;

LOOP
FETCH cur_sal
INTO x;

EXIT WHEN cur_sal%NOTFOUND;

IF (x > y)
THEN
y := x;
END IF;
END LOOP;

CLOSE cur_sal;

DBMS_OUTPUT.put_line ('max sal without using max function = ' || y);
END;
/```

----------------------------

the Question is : how suppose that's happen ?
what's the secret in those codes ?

it's just new idea for me , but I don't understand !

is there any name for this way in ( OracleŽ Database PL/SQL User's Guide and Reference ) ?
if not , so please anyone give me any help to understand those codes !
Re: calculate the max and sum value by weird way !!! [message #342574 is a reply to message #342573] Fri, 22 August 2008 18:32
 ThomasG Messages: 3191Registered: April 2005 Location: Heilbronn, Germany Senior Member
That is just a plain and simple anonymous PL/SQL block with a cursor and a loop in it.

Database PL/SQL User's Guide and Reference

Re: calculate the max and sum value by weird way !!! [message #342575 is a reply to message #342574] Fri, 22 August 2008 18:39
 BlackSwan Messages: 25130Registered: January 2009 Location: SoCal Senior Member
 ThomasG wrote on Fri, 22 August 2008 16:32 That is just a plain and simple anonymous PL/SQL block with a cursor and a loop in it. Database PL/SQL User's Guide and Reference

Thanks for the explanation, but please provide details on how this addition operation works; as in 1 + 2 = 3.
Re: calculate the max and sum value by weird way !!! [message #342576 is a reply to message #342575] Fri, 22 August 2008 18:45
 ThomasG Messages: 3191Registered: April 2005 Location: Heilbronn, Germany Senior Member
 Quote: Thanks for the explanation, but please provide details on how this addition operation works; as in 1 + 2 = 3.

Well, now that you bring it up that has quite a bit of a background story.

In the beginning there was just "nothing" and "something".

That system later got revised into "nothing", 1, 2, 3, "lots".

Since a lot of people had a lot of "nothing" they decided to split that into "null" and "zero" to be able to put it in different bags.

Then they added a lot of numbers in the middle and renamed "lots" to "buffer overflow", which is basically what we work with today.

PS: Oh, I forgot about negative numbers. We have those, too. They are quite easy to visualize: It's like a room where 10 people go in and 15 people come out. Now 5 people have to go in for it to be empty again.

[Updated on: Fri, 22 August 2008 18:59]

Report message to a moderator

Re: calculate the max and sum value by weird way !!! [message #342590 is a reply to message #342573] Sat, 23 August 2008 00:51
 flyboy Messages: 1834Registered: November 2006 Senior Member
> but please provide details on how this addition operation works; as in 1 + 2 = 3.

By the way, how would you SUM values e.g. 3,4,5. Would you create table with 3 rows and run a query with SUM on them? Are you really unable to count it yourself without any computer aid?
Re: calculate the max and sum value by weird way !!! [message #342594 is a reply to message #342576] Sat, 23 August 2008 01:17
 Michel Cadot Messages: 64269Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

A nice laugh at Saturday breakfast time, many thanks for it that's a week-end that well starts.

Michel
Re: calculate the max and sum value by weird way !!! [message #342602 is a reply to message #342573] Sat, 23 August 2008 01:43
 Frank Messages: 7880Registered: March 2000 Senior Member
 Hany Freedom wrote on Sat, 23 August 2008 01:19 is there any name for this way

Yeah: stupidity.

This is really the most stupendous way to calculate the max value..
Ever heard of ORDER BY?

I suspect that you are just trolling..
Re: calculate the max and sum value by weird way !!! [message #342619 is a reply to message #342602] Sat, 23 August 2008 04:40
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
LMAO. One of the funniest threads I have seen in a looooong time.
Re: calculate the max and sum value by weird way !!! [message #342626 is a reply to message #342602] Sat, 23 August 2008 06:09
 Hany Freedom Messages: 256Registered: May 2007 Location: Egypt Senior Member
Frank wrote on Sat, 23 August 2008 09:43
 Hany Freedom wrote on Sat, 23 August 2008 01:19 is there any name for this way

Yeah: stupidity.

This is really the most stupendous way to calculate the max value..
Ever heard of ORDER BY?

I suspect that you are just trolling..

I know that there are better ways from what I said ......... but I was just talking about the new idea that may help us in another calculates

anyway thanks for all
Re: calculate the max and sum value by weird way !!! [message #342628 is a reply to message #342626] Sat, 23 August 2008 06:36
 flyboy Messages: 1834Registered: November 2006 Senior Member
 Hany Freedom wrote on Sat, 23 August 2008 13:09 I know that there are better ways from what I said ......... but I was just talking about the new idea that may help us in another calculates

Yes, the more difficult this approach is, the worse it performs (in comparition with SUM). So I do not see where could it help (except in making the code less transparent and slowing down the execution).

 flyboy wrote on Sat, 23 August 2008 07:51 By the way, how would you SUM values e.g. 3,4,5
Maybe you know other methods, but I would do it in the same way the "new idea" calculates it. So I would rather re-classify it to the "old idea" (procedural, non-SQL).
 Previous Topic: Job scheduler - SQL Error ORA-06550 Next Topic: Problem in creating abcdic format file
Goto Forum:

Current Time: Mon Jan 16 21:12:20 CST 2017

Total time taken to generate the page: 0.15571 seconds