MAX Function

Toad for Oracle
1

Hi,

do you know why toad is returning no rows when fetching the maximum value of a column which also has NULL values?

Here the code I’ve tested:

create table test_max
(ID NUMBER(2)
,VALUE NUMBER(4)
);

insert into test_max values(1, 0);
insert into test_max values(2, 999);
insert into test_max values(3, NULL);

commit;

select max(value)
from test_max;

no rows selected.

If I’m running the same select in plsql (also in Toad), I’m getting the right result:

set serveroutput on

declare
l_max_value test_max.value%TYPE;
begin
select max(value)
into l_max_value
from test_max;

dbms_output.put_line(l_max_value);
end;
/

999

Thanks,

Bogdan

Message was edited by: bac_468

2

Hi Began,

do you know why toad is returning no rows when fetching the
maximum value of a column which also has NULL values?

I'm sorry, no. I tried your code here using Toad 10.0.41 and it's
returning 999 correctly.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

3

Hi Began,
Sorry, spell checker changed your name!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

4

I agree with Norm. No failures.

Oracle 10g, Toad 10.5.0.16

chris

5

I’ve tested the code in Oracle 10g, with Toad 9.7.2.5. I’ll try to install Toad 10 and test it again…

Message was edited by: bachisb

6

I’ve tested the code in Toad 10 and it’s returning the right value. It seems to be a bug in the 9.7.5 version.

Thanks anyway,

Bogdan