Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Windows 7 Home premium and Oracle 10g Compatibility

785713Jul 17 2010 — edited Jul 18 2010
hi
I am trying to install Oracle 10.2.0.4 for vista x64 on Windows 7 Home premium
i tried too much, but doesnt find any solutions to install it on windows 7
Setup doesnot starts even if i install it in Windows xp (Service Pack 2) compatibility settings
plz tell me some solution
or suggest me some alternatives, if it doesnot have any solutions

waiting for ur kind response

Edited by: 782710 on Jul 17, 2010 3:24 PM

Comments

Twinkle
try this,

SQL> select greatest('hello','zzzzz','aaaaa',nvl(' ',0)) from dual;

GREAT
-----
zzzzz
Sven W.
Hm... is -999999 greater than nothing?
Answer: It depends (on your specific busines requirements).

Solution is simple: Add a nvl around each of your values and replace it with the value that suits your specific needs.

Simple thing to remember: each function will return NULL when one of the parameters is NULL.

There is only a handful of exceptions:
NVL, NVL2, DECODE, LNNVL, NULLIF, COALESCE and CASE

Edited by: Sven W. on Sep 11, 2009 11:01 AM
Karthick2003
NULL means an Unknown value correct? So when we pass an Unknown value to a function that compares values it cant handle an Unknown value so it just says the result is als Unknows.

Only option coming in my mind is to use NVL with all the passing values.
Alessandro Rossi
That's not a solution!!

What I posted is just a simple way to show how it works. When I call the greatest function I don't have idea about the value of the expressions I put in it, else I would also know the greatest too without using the greatest function.

I hope this is won't offend you but this problem, in my opinion, looks really serious.
Thanks anyway.

Bye Alessandro
Twinkle
I would also know the greatest too without using the greatest function.
I know that, but the only way to avoid null values is use NVL.
you can put those columns in NVL where there are chances of having null values.(columns which are not primary keys, or do not have not null constraint).

Also check this
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:524526200346472289

If this doesnot satisfy your question then give us similar scenerio which explains
When I call the greatest function I don't have idea about the value of the expressions I put in it
Edited by: Twinkle on Sep 11, 2009 3:36 PM

Edited by: Twinkle on Sep 11, 2009 3:40 PM
Sven W.
The MAX function will also give you the greatest value. It does however compare rows not columns. Good thing is it ignores NULL values. So you might think how to transpose/move your different values into a nice and database appropriate row structure.
Alessandro Rossi
Sven W. wrote:
Hm... is -999999 greater than nothing?
Answer: It depends (on your specific busines requirements).
Yes that's right! My business requirements says that the expressions I pass as null to greatest are values that not exist, so I need to not consider nulls.
Solution is simple: Add a nvl around each of your values and replace it with the value that suits your specific needs.

Simple thing to remember: each function will return NULL when one of the parameters is NULL.

There is only a handful of exceptions:
NVL, NVL2, DECODE, LNNVL, NULLIF, COALESCE and CASE
I'm already using a substr(greatest(x||exp1, ... x||expn),2) workaround in my project but this is very ugly perhaps this workaround makes a simple task to become more complex and sensibly longer on heavy loads.


Bye Alessandro
Sven W.
I really think you should find a way to use MAX instead of GREATEST.
Alessandro Rossi
Sven W. wrote:
I really think you should find a way to use MAX instead of GREATEST.
To use max i should pivot my table first and that would be more complex again.

I'll wait for the next new oracle useful functions GREATESTNNV and LEASTNNV for the next few centuries.


Thanks anyway.
Bye Alessandro

Edited by: Alessandro Rossi on 11-set-2009 12.27
BluShadow
Alessandro Rossi wrote:
Hi everybody

I just spot a quite strange behavior on the greatest function: when one of its parameters is null, it returns null.
I also don't think this is what everybody expects from it because there are several cases where this is unacceptable.
"everybody"? you sure about that? ;)

As Karthick correctly points out, NULL is the equivalent of an "unknown" value.

If you have a bag of numbered balls with 3 numbers in it, you know two of the numbers but the 3rd one is unknown to you, which of those numbers is the greatest? Of course the answer is unknown. That sounds perfectly acceptable to me and is exactly what I expect.
Sven W.
Alessandro Rossi wrote:
Sven W. wrote:
I really think you should find a way to use MAX instead of GREATEST.
To use max i should pivot my table first and that would be more complex again.
I think this might normalize your data. the little you have shown lets me think that it is pretty de-normalized.

Greatest is rarely used. Max is used all the time.
The simple reason is: Data that on wants to compare comes usually in rows, not in columns. Especially for an unknown number of data.

Edited by: Sven W. on Sep 11, 2009 2:45 PM
Alessandro Rossi
If you have a bag of numbered balls with 3 numbers in it, you know two of the numbers but the 3rd one is unknown to you, which of those numbers is the greatest? Of course the answer is unknown. That sounds perfectly acceptable to me and is exactly what I expect.
Your example is not well proposed. I know you interpret null as unknown but in many cases null means missing. So ....

You have a bag with some numbered balls and some unnumbered balls. What could be the greatest number you can spot on the balls?

Can you spot the difference between unknown and nonexistent?

I was just feeling the need of a greatest (and least function too) that ignores null values, instead of invalidating all my job.

If you don't, just ignore this thread and keep working on your things and have a good life but don't leave such a stupid message! I have a problem and you can't come to me and say that is not a problem, because the problem is there.

And why don't you say that it's strange that MAX() and MIN() ignore nulls then? If I would think with your head, I would also say that if I have an unknown value in a set I can't say witch one is the greatest or the least and then they should return null like GREATEST() and LEAST() do giving an "ACCEPTABLE" result . Isn't this right?


Bye Alessandro

Edited by: Alessandro Rossi on 11-set-2009 14.55
Alessandro Rossi
It's not denormalized data it's just a table with some date fields that has to be joined with another one on the greatest criteria.

I don't think this could be so unusual.


Bye Alessandro
Karthick2003
Can you spot the difference between unknown and nonexistent?
In RDBMS

UnKnown = The Data exist in the database but it value is Unknown = NULL
NonExistant = The Data does not exist in the database

I guess you are mixing it here.
And why don't you say that it's strange that MAX() and MIN() ignore nulls then?
ANSI has defined Aggrigate functions to skip NULL.

A system has been created and the world has accepted the system. If you go against it then its your call. There is no point blaming the system.
Aketi Jyuuzou
We have to use coalesce any times. :D
http://www.geocities.jp/oraclesqlpuzzle/10-179.html


There is a good news that greatest of postgreSQL igonre nulls. B-)
http://www.postgresql.jp/document/current/html/functions-conditional.html

Greatest of Oracle and db2 do not ignore nulls
Frank Kulash
Hi,

You can use MIN and MAX with odci types to get what you want.

For example:
SELECT	MAX (column_value)
FROM	TABLE ( sys.odcinumberlist ( -999
	      		       	   , 1
			       	   , 999
			       	   , NULL
			       	   )
	      )
;
If the arguments are coming from some table, you can do a scalar sub-query, like this:
SELECT  ename
,	job
,	( SELECT  MIN (column_value)
	  FROM	  TABLE ( sys.odcivarchar2list ( ename
	  	  	  		       , job
					       , NULL
					       , 'FOO'
					       )
			)
	) AS least_string
FROM	scott.emp
;
Output from the last query:
NAME      JOB       LEAST_STRING
---------- --------- ---------------
SMITH      CLERK     CLERK
ALLEN      SALESMAN  ALLEN
WARD       SALESMAN  FOO
JONES      MANAGER   FOO
MARTIN     SALESMAN  FOO
BLAKE      MANAGER   BLAKE
CLARK      MANAGER   CLARK
SCOTT      ANALYST   ANALYST
KING       PRESIDENT FOO
TURNER     SALESMAN  FOO
ADAMS      CLERK     ADAMS
JAMES      CLERK     CLERK
FORD       ANALYST   ANALYST
MILLER     CLERK     CLERK
BluShadow
Alessandro Rossi wrote:
If you have a bag of numbered balls with 3 numbers in it, you know two of the numbers but the 3rd one is unknown to you, which of those numbers is the greatest? Of course the answer is unknown. That sounds perfectly acceptable to me and is exactly what I expect.
Your example is not well proposed. I know you interpret null as unknown but in many cases null means missing. So ....

You have a bag with some numbered balls and some unnumbered balls. What could be the greatest number you can spot on the balls?

Can you spot the difference between unknown and nonexistent?
Ah, but that's where you're going wrong.
You are providing a NULL value as a parameter. Therefore it is not nonexistent. You are telling the function there is a value, but that the value is unknown. If it were nonexistent then you would have only provided 2 parameters instead of 3.

If you're referring to NULL in programming languages (especially Oracle SQL and PL/SQL), by considering null to always mean "unknown" you will always be on the right track to understanding. Consider it nonexistent and you will confuse yourself. Oracle treats it as "unknown" and that is how I understand it and, in truth, you'd be better off understanding it that way too. ;)
If you don't, just ignore this thread and keep working on your things and have a good life but don't leave such a stupid message! I have a problem and you can't come to me and say that is not a problem, because the problem is there.
I'm sorry you have taken offence at it (your problem not mine). I was merely explaining a valid way to consider NULLs within Oracle and other programming languages. I have always considered NULL to mean Unknown and that has always worked. The only time I have ever encountered people experiencing problems understanding something because of NULL is when they do not consider it to mean "unknown".

The analogy of the bag was intended to be helpful.
And why don't you say that it's strange that MAX() and MIN() ignore nulls then? If I would think with your head, I would also say that if I have an unknown value in a set I can't say witch one is the greatest or the least and then they should return null like GREATEST() and LEAST() do giving an "ACCEPTABLE" result . Isn't this right?
Part of SQL throughout history has been that aggregate functions typically eliminate nulls from their calculations. It's an ongoing area of controversy and can lead to some unpredictable results... e.g.

The average of 4 rows of values, one containing null...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 150 as x from dual union all
  2             select 200 from dual union all
  3             select 250 from dual union all
  4             select null from dual)
  5  --
  6  select avg(x)
  7* from t
SQL> /

    AVG(X)
----------
       200
... is calculated as the sum of the non-null values divided by the number of non-null rows.

However you may typically expect the null row to be counted, such that the result is the sum of all the values divided by the total number of rows..., effectively treating the null as a zero...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 150 as x from dual union all
  2             select 200 from dual union all
  3             select 250 from dual union all
  4             select 0 from dual)
  5  --
  6  select avg(x)
  7* from t
SQL> /

    AVG(X)
----------
       150

SQL>
... which gives a different result.

That's just one of the things with aggregate functions. Always has been. To be consistent they too should return NULL, and then people would be forced to enter a value on their database to signify a 'default' of some sort i.e. 0 where the data is non-existent.

So, yes, I agree with you that this isn't ideal. As such, there are just the two things to remember...

1) Aggregate functions eliminate nulls
2) Nulls everwhere else should be treated as "unknown".

If you really wanted to get into the nitty gritty of it you could argue that both of these should return null too..
SQL> select 1+null from dual;

    1+NULL
----------


SQL> select 'fred'||null from dual;

'FRE
----
fred

SQL>
;)
Sven W.
Alessandro Rossi wrote:
It's not denormalized data it's just a table with some date fields that has to be joined with another one on the greatest criteria.

I don't think this could be so unusual.

Bye Alessandro
You're right if you have just some date fields. That would be a quite typical case. In this case you always go with the NVL logic or with some special CASE handling. I had the impression that you have many more fields then just "some".

probably that impression cam from this example of yours (which somehow doesn#t look like date values...)
substr(greatest(x||exp1, ... x||expn),2)
Edited by: Sven W. on Sep 11, 2009 3:38 PM
Hoek
1) Aggregate functions eliminate nulls
Hmz...Thanks! You've hereby inspired me to start writing a script for a Kill Null - movie ;)

starts whistling
Alessandro Rossi
So if you may see a valid point on missing a sort of GREATESTNNV and LEASTNNV functions there is nothing else to say. I agree with your point too, I know that null values should be avoided when possible, but it's not always possible to find perfectly designed environments to work on. I opened this thread to look for a solution of my problem and instead of finding it I had a discussion with you. I hope you don't feel offended about it, because it was not my intention.


Bye Alessandro
66470
I cannot see a need of GREATESTNNV or LEASTNNV as you can easily mask your parameters with the NVL function and choose an approriate value to replace the nulls...

And even if we agreed in the point that it would be nice to have those functions, it would not help you to solve your problem, as this functions are not around yet nor in 11g nor in release 2 and i doubt they will in oracle 12...
Alessandro Rossi
Oh you spot it!

I just wanted to show that I knew a workaround for it too, but it was not what I was looking for.

I didn't want to expose my implementation just because I didn't consider it good for the examples I proposed. But if NLS_DATE_FORMAT would be 'yyyy-mm-dd hh24:mi:ss' the subtr method would be fine.


This is code I really used
nullif(greatest(nvl(g_scade,date'0000-01-01'),nvl(g_doceme,date'0000-01-01'),nvl(g_inser,date'0000-01-01')),date'0000-01-01')
But using nullif(nvl) method in this case would fail without noticing any error while using the subtr one you're only limited by the fact that you can't use strings longer than 3999 bytes.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
  2  /

Session altered.

SQL>
SQL> select nullif(greatest(nvl(null,date'0001-01-01'),nvl(date'0001-01-01',date'0001-01-01')),date'0001-01-01')
  2  from dual
  3  /

NULLIF(GREATEST(NVL
-------------------


SQL>
SQL> select substr(greatest('x'||null,'x'||date'0001-01-01'),2)
  2  from dual
  3  /

SUBSTR(GREATEST('X'
-------------------
0001-01-01 00:00:00

SQL>
Bye Alessandro

Edited by: Alessandro Rossi on 11-set-2009 16.49
Alessandro Rossi
It is a missing feature.
Alessandro Rossi
horten69 wrote:
I cannot see a need of GREATESTNNV or LEASTNNV as you can easily mask your parameters with the NVL function and choose an approriate value to replace the nulls...
Don't worry about it your needs are not like mine. We're all different from each other and it's not that bad. I wouldn't be that happy to meet just people like me I like comparing my opinions with others and of wouldn't be different from mine I wouldn't need it.
And even if we agreed in the point that it would be nice to have those functions, it would not help you to solve your problem, as this functions are not around yet nor in 11g nor in release 2 and i doubt they will in oracle 12...
Nothing more right than that but probably this thread could inspire someone there to introduce them. As someone said there is a little competitor who already did it, so there could be one time when Oracle would do it too.

For this moment I'll wait for it or for the time when Postgre SQL will lead the database market.


Bye Alessandro
user12019680
just my two cents

I had to do this to get the greatest of four dates and I used nvl and greatest

I didn't want to use some arbitrary date for the second argument of the nvl, I was not sure how far back the dates went

I was lucky in that one of my dates was a mandatory field so I just used that one for the second argument

this seems to work for me

select max(greatest(
date1, -- this is the mandatory one
nvl(date2,date1),
nvl(date3,date1),
nvl(date4,date1)))
from mytab
where key=...
Paul Horth
Why are you responding to a 4 year old thread?
David Balažic
Maybe because he has the best answer to the question?
Yep that's it. An excellent answer to a thread where half of replies are useless chit chat and also some proposals that might work in certain circumstances.
His solution also works only in certain circumstances, so at least it nicely complements the other answers.
10 points from me...
1 - 27
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 15 2010
Added on Jul 17 2010
4 comments
1,582 views