Home » SQL & PL/SQL » SQL & PL/SQL » Maximum Value Wihtout using Oracle Functions
Maximum Value Wihtout using Oracle Functions [message #395138] Tue, 31 March 2009 08:03 Go to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hi,

I have requirement where-in I have to display the maximum value of two columns along with the actual two columns from a table wihtout using Oracle built-in functions.

Let us say I have a table name EMP with SALARY and AMOUNT as two columns.

The data in the table EMP will be as follows:

SALARY AMOUNT
--------------------
10000 5000
2000 NULL
NULL 1000
3000 6000

I need to display the data as follows with out using any Oracle Built-in functions like MAX,CASE,DECODE,>,< etc..

MAX VALUE of SALARY and AMOUNT SALARY AMOUNT
--------------------------------------------------------
10000 10000 5000
2000 2000 NULL
1000 NULL 1000
6000 3000 6000

Please let me know is it possible.

Thanks,
Ash


Re: Maximum Value Wihtout using Oracle Functions [message #395144 is a reply to message #395138] Tue, 31 March 2009 08:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Hope you have studied Maths...
case when a-b >= 0 then a else b end
By
Vamsi

[Updated on: Tue, 31 March 2009 08:20]

Report message to a moderator

Re: Maximum Value Wihtout using Oracle Functions [message #395145 is a reply to message #395144] Tue, 31 March 2009 08:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

with out using any Oracle Built-in functions



Why?

If you cant use ANY Oracle Built-in functions, then you can't even connect to the database, since that already triggers a lot of built-in functions in the background.
Re: Maximum Value Wihtout using Oracle Functions [message #395148 is a reply to message #395144] Tue, 31 March 2009 08:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When did CASE stop being a function?

Explain why you have this requirement not to use Oracle functions?

If the answer is "because it's my homework", or "because I wanted to see if it's possible", then you're on your own.
Re: Maximum Value Wihtout using Oracle Functions [message #395154 is a reply to message #395148] Tue, 31 March 2009 08:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And when did "<" BECOME a function?

I always thought it was an operator. Cool
Re: Maximum Value Wihtout using Oracle Functions [message #395156 is a reply to message #395154] Tue, 31 March 2009 08:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing that they're looking for something involving subqueries returning the first row of an ordered set.

Except that you'd have to use ROWNUM (function) or ROW_NUMBER (analytic function) to do that.
Re: Maximum Value Wihtout using Oracle Functions [message #395159 is a reply to message #395156] Tue, 31 March 2009 08:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Otherwise
select a,a,b from table1
where a >= b
union
select b,a,b from table1
where a < b;
Something like that may help you.
Here also you should amend the NULL cases to not use nvl.

By
Vamsi
Re: Maximum Value Wihtout using Oracle Functions [message #395162 is a reply to message #395138] Tue, 31 March 2009 09:10 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
That still violates OP's rules with use of < Cool
Re: Maximum Value Wihtout using Oracle Functions [message #395168 is a reply to message #395138] Tue, 31 March 2009 09:43 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
CASE is expression, < is operator, so I think it is correct to use them in this homework (as the requirement is easily achievable by Oracle GREATEST function).

However, treating of NULLs shall be more precise than vamsi's examples. As this is homework assignment, I will let it on original poster.
Re: Maximum Value Wihtout using Oracle Functions [message #395171 is a reply to message #395168] Tue, 31 March 2009 09:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
flyboy wrote on Tue, 31 March 2009 15:43
CASE is expression, < is operator, so I think it is correct to use them in this homework

I disagree, the OP specifically stated that it must be accomplished
Quote:
with out using any Oracle Built-in functions like MAX,CASE,DECODE,>,<
Granted, some of these are not necessarily functions (although if you search the stdspec.sql file you will find "<" and ">" defined as functions... however, since thety are specifically excluded, i would say that it is NOT correct to use them.

[Updated on: Tue, 31 March 2009 09:51]

Report message to a moderator

Re: Maximum Value Wihtout using Oracle Functions [message #395176 is a reply to message #395138] Tue, 31 March 2009 10:02 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hello All,

First let me clarify that I am very well aware of all the functions...if you can understand clearly...I just mentioned the restrictions specifying not using of functions and operators...

If you can answer then help me..other wise it is upto you..
Why people always ask for requiremnt..I mentioned clearly what I need..if is is possible..then let me know..otherwise say it directly that it's not possible...

And I think some people don't understand the question or couldn't read the question properly...I said with out using CASE,MAX,>,<...still they mentioned in their posts using CASE,< etc..

I thought this forum is where I can get most of the solutions..but here people just crticize rather than answering the questions...

Try to help..but don't try to be smart
Re: Maximum Value Wihtout using Oracle Functions [message #395189 is a reply to message #395176] Tue, 31 March 2009 10:30 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
AWESOME attitude to take. Who exactly do you think populate these forums? Certainly not your minions to do as you demand. Reel you neck in if you want assistance. People ask for requirements because your request sounds rather foolish. i.e. how can i perform a task without using the tools that are already in place to perform exactly that task. Well if someone asked me how to find out if one value equalled another without using the equals operator, I would certainly want to know why such a strange requirement was necessary. i would want to know exactly what the real requirement was before i put too much effort into a nonsensical or pointless question.
So in future, If you don't like the responses that you get, feel free to ask for a refund on your membership.

[Updated on: Tue, 31 March 2009 10:40]

Report message to a moderator

Re: Maximum Value Wihtout using Oracle Functions [message #395196 is a reply to message #395189] Tue, 31 March 2009 10:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
I mentioned clearly what I need


No you didn't.

If the requirement really is

Quote:
to display the data as follows with out using any Oracle Built-in functions


then the only answerer is "You can't" since as I said you can't even connect to the database without using some built-in functions somewhere in the background.



Re: Maximum Value Wihtout using Oracle Functions [message #395238 is a reply to message #395176] Tue, 31 March 2009 16:51 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I thought people ask here question when they get stuck with real problem. But some are just excessively bored and ask eccentric question without any practical sense. All right, if you like it; however do not expect many meaningful responses in this case.
By the way, I see no note about operators and expressions in the initial post, so I would not classify it clear.
Quote:
Oracle Built-in functions like MAX,CASE,DECODE,>,< etc..

In the opposite, I would classify this statement wrong, as (according to Oracle SQL Reference documentation book) only MAX and DECODE are functions. By the way, I would like to see solution of this problem using MAX function (just cannot imagine it now).

Well, if even connecting to Oracle database and treating its data is expected as using Oracle functions, then it is impossible. Otherwise, install any other database (SQL Server, MySql, Postgre, ...), maintain a connection to Oracle database and query Oracle database from there. Of course, you will use the same program constructions (CASE, <), but they will not be Oracle ones.

[Edit: Added note about MAX function]

[Updated on: Tue, 31 March 2009 16:55]

Report message to a moderator

Re: Maximum Value Wihtout using Oracle Functions [message #395282 is a reply to message #395144] Wed, 01 April 2009 01:01 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
I don't undersatnd people who instead of answering to the specific query try to get away from the question stating we need Oracle built-in functions for getting connected to oracle database...and other suggestions...and some people without actually reading the complete question posted the suggestions using CASE and operators..I do know that by using CASE,DECODE,MAX,>,etc..it can be done...

I just asked is it possible or not without using CASE,MAX,<,> etc...if it is then let me know else you can ignore this topic rather than bashing at me...lecturing me abt connecting to oracle database and other databses...

Just be specific to the question...
Re: Maximum Value Wihtout using Oracle Functions [message #395283 is a reply to message #395282] Wed, 01 April 2009 01:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I predict a wonderful future for you here at OraFAQ if you keep up that attitude.
Lots of interesting answers, lots of useless discussions.

Now, you ask for something out of the ordinary, and you are quite vague about your exact requirements (the restrictions on what you can and cannot use: < and > are not functions, so what falls in the category "etc" ?)
Is it so strange that people ask you why you want to know this?
All too often, people come here looking for a solution to a problem. They looked at the problem and thought of a possible way to solve it. They get stuck trying that and ask how to implement that solution.
When we ask more about the problem, often there's a far simpler solution at hand.
Re: Maximum Value Wihtout using Oracle Functions [message #395286 is a reply to message #395138] Wed, 01 April 2009 01:16 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
I do understand people come here for solutions...and that is why I have joined this forum(recomended by my friend)..but I see some of them here instead of providing suggestions...laugh at the quesions and make fun of the questions...I know these are a simply queries for most of them and laughable stock for some of them...but there are some people who are new to oracle and need to know more...

Anyhow I opologise for my comments..and thanks for your valuable suggestions..
Re: Maximum Value Wihtout using Oracle Functions [message #395291 is a reply to message #395286] Wed, 01 April 2009 01:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Great, now let's get back to your actual question:
Could you be more specific on the "banned" functions?
Built-in functions would be things like nvl, decode, greatest, max. Those are quite easy to distinguish (They have are used with parameter(s) in parentheses)
Do you really also need to ban operators like < and > ? What about = ?
How about analytical functions? (lag, lead, etc)
Re: Maximum Value Wihtout using Oracle Functions [message #395295 is a reply to message #395291] Wed, 01 April 2009 01:49 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Yeah..we should not use NVL,CASE,MAX,GREATEST,DECODE and also operators like <,>,=...not even analytical functions...just a query where-in we can get the required ouput(mentioned in my 1st post)...

In my project they want to use it in a sub-query where they can use it even in SQL server as it has to support(use the same logic) in both the databases..I know we can use fucntions both in Oracle and SQL server side to do this...but I was told not use any functions...

Any help is appreciated..
Re: Maximum Value Wihtout using Oracle Functions [message #395307 is a reply to message #395295] Wed, 01 April 2009 02:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I'm afraid banning all those will increase the chances of writing non-compatible queries..

Without using <, > and = in the where-clauses, I for me would not know how to do this.
Very curious if anyone can come up with anything...
Re: Maximum Value Wihtout using Oracle Functions [message #395318 is a reply to message #395307] Wed, 01 April 2009 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It should be hard to find the greatest value among two without comparing. It is like making choose the red shirt among a red and green ones to a blind man/woman.

Regards
Michel
Re: Maximum Value Wihtout using Oracle Functions [message #395327 is a reply to message #395307] Wed, 01 April 2009 02:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
In my project they want to use it in a sub-query where they can use it even in SQL server


AHA! Not THAT is a requirement that makes sense. Stating that you need to do something "Without built in functions" makes no sense without knowing WHY.

Now that you told us WHY we can actually answer:

If you want to write SQL Compatible between different databases, you can stick to the ANSI SQL Standard.

For example, this works both in Oracle and SQL Server, since CASE actually *IS* part of the ANSI SQL standard, so in that sense it is not a "Oracle-Only" Built-in function, so for the "Has to run on SQL Server too" requirement, there is no need too avoid it.

CREATE TABLE test_tab (n1 int, n2 int);

INSERT INTO test_tab VALUES (1,2);
INSERT INTO test_tab VALUES (2,1);

INSERT INTO test_tab VALUES (2,3);
INSERT INTO test_tab VALUES (3,2);


SELECT CASE WHEN n1 > n2 THEN n1 ELSE n2 END 
FROM test_tab; 


Re: Maximum Value Wihtout using Oracle Functions [message #395358 is a reply to message #395327] Wed, 01 April 2009 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But CASE and > are not allowed too.
Actually OP did not say if he wants to do it in SQL. Does he?

Regards
Michel
Re: Maximum Value Wihtout using Oracle Functions [message #395364 is a reply to message #395358] Wed, 01 April 2009 03:57 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, I picked the "Has to run on different kinds of database servers" requirement, and answered that, since that is the only thing that makes sense as a requirement so far.

Which again proves that the most difficult thing in the life of a programmer is to figure out what the actual requirement is. Very Happy
Re: Maximum Value Wihtout using Oracle Functions [message #395390 is a reply to message #395364] Wed, 01 April 2009 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ThomasG wrote on Wed, 01 April 2009 10:57
...
Which again proves that the most difficult thing in the life of a programmer is to figure out what the actual requirement is. Very Happy

Laughing

Re: Maximum Value Wihtout using Oracle Functions [message #395435 is a reply to message #395138] Wed, 01 April 2009 07:52 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Thanks for your VALUABLE suggestions....it can be done and below is the solution...

SELECT ((SALARY+AMOUNT)+ABS(SALARY-AMOUNT))/2 MAX_VALUE,
SALARY,
AMOUNT
FROM table_name

Re: Maximum Value Wihtout using Oracle Functions [message #395440 is a reply to message #395435] Wed, 01 April 2009 08:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Does not work with NULLs, ABS is definetely a function.
Re: Maximum Value Wihtout using Oracle Functions [message #395464 is a reply to message #395440] Wed, 01 April 2009 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Wed, 01 April 2009 15:01
Does not work with NULLs, ABS is definetely a function.


INDEED!

But maybe not for OP. Grin

Regards
Michel
Re: Maximum Value Wihtout using Oracle Functions [message #395471 is a reply to message #395138] Wed, 01 April 2009 09:50 Go to previous messageGo to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hello...I know ABS is a function...but atleast I tried not using CASE,<,>,DECODE,MAX which I think is far better than other people who used CASE,MAX,<,> even after mentioning specifically not to use CASE,<,>,MAX and also better than the ones who make a fun of such questions...

And once again thanks for you all..and good bye to this forum where instead of providing valuable suggestions and helping others make fun and laugh....

[Updated on: Wed, 01 April 2009 09:53]

Report message to a moderator

Re: Maximum Value Wihtout using Oracle Functions [message #395531 is a reply to message #395471] Wed, 01 April 2009 15:04 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
If between can be used......
I know that internally it will be treated as >= and <= but, if it meets OP's restrictions, the following can be used.
create table table1 (a number, b number);

insert into table1 values(100,50);
insert into table1 values(50,100);
insert into table1 values(100,-50);
insert into table1 values(-50,100);
insert into table1 values(-100,50);
insert into table1 values(50,-100);
insert into table1 values(-100,-50);
insert into table1 values(-50,-100);
insert into table1 values(100,null);
insert into table1 values(null,50);
insert into table1 values(null,null);

select * from table1;

select a,b,a max_ab from table1
where (b between b and a
   or b is null)
union
select a,b,b from table1
where (b not between b and a
   or a is null);
I really overlooked the OP, in my previous posts. I'm new to my contact lenses Cool

By
Vamsi
Re: Maximum Value Wihtout using Oracle Functions [message #395600 is a reply to message #395471] Thu, 02 April 2009 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ashreddy wrote on Wed, 01 April 2009 16:50
Hello...I know ABS is a function...but atleast I tried not using CASE,<,>,DECODE,MAX which I think is far better than other people who used CASE,MAX,<,> even after mentioning specifically not to use CASE,<,>,MAX and also better than the ones who make a fun of such questions...

And once again thanks for you all..and good bye to this forum where instead of providing valuable suggestions and helping others make fun and laugh....

Well, next time you post here (or the next forum you will be posting on), don't word your questions as follows "with out using any Oracle Built-in functions like MAX,CASE,DECODE,>,< etc.." if you only mean that you cannot use MAX, CASE, DECODE, < and >

Also you should try to take a less hostile attitude. You seem to think everybody is out to get you, whereas everyone here is just trying to get your actual requirements in order to being able to help you.

[Updated on: Thu, 02 April 2009 00:54]

Report message to a moderator

Re: Maximum Value Wihtout using Oracle Functions [message #396880 is a reply to message #395600] Wed, 08 April 2009 04:49 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Just for fun -if ABS should be "forbidden" too, use:
SELECT a, b, (SIGN(a - b) * (a - b) + a + b) / 2 max_value
  FROM table1

This also can't compare NULL-Values.
Re: Maximum Value Wihtout using Oracle Functions [message #396882 is a reply to message #396880] Wed, 08 April 2009 04:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
And also uses an Oracle function.
Re: Maximum Value Wihtout using Oracle Functions [message #396912 is a reply to message #396882] Wed, 08 April 2009 06:44 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
of course Cool
Previous Topic: DBMS_SCHEDULER - job to be created
Next Topic: insufficient privilages in create view
Goto Forum:
  


Current Time: Thu Dec 08 05:57:08 CST 2016

Total time taken to generate the page: 0.21964 seconds