Solved

min(timestamp) in select statement return null

Posted on 2006-07-01
1
1,193 Views
Last Modified: 2008-02-01
I am selecting date type data in following manner. Why it is returning null while condition became falls.

select min(timestamp) from admassist.sye_error_log where SQL_ERRM is not null and sql_code not like '%20003%' ;
If I am using baove statement, it returns 1 null rows.
But if I am wrintting following, it's not selecting anything.
select timestamp from admassist.sye_error_log where SQL_ERRM is not null and sql_code not like '%20003%'


why select with min() funciton return 1 raws that's balnk. I am not expecting any raw to return.

Thank you
~Keyur
0
Comment
Question by:Keyurkumar
1 Comment
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 17024854
As soon as you use aggregate functions in your SQL, you must be aware that empty sets might come into play. The example below shows how aggregate functions react to empty sets. Apparently, COUNT returns zero whereas AVG, SUM, MAX, and MIN return null. This behavior does make a certain amount of sense. If you have no values to count, it's fair to say that you have zero values, whereas you can't really come up with, say, a maximum value without at least one value from which to choose. We can make a reasonable argument that SUM should return zero instead of null, that the sum of no values is zero, but Oracle's implementation of the behavior we describe here is fully compliant with the SQL standard.

SELECT COUNT(EMPNO),AVG(EMPNO),SUM(EMPNO),MAX(EMPNO),MIN(EMPNO)
FROM EMP_M
WHERE 1 = 2;

COUNT(EMPNO)           AVG(EMPNO)              SUM(EMPNO)        MAX(EMPNO)          MIN(EMPNO)
________________     _____________      _____________     _____________     _____________
0



Try:

select timestamp from admassist.sye_error_log where timestamp = (
select min(timestamp) from admassist.sye_error_log where SQL_ERRM is not null and sql_code not like '%20003%') ;
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question