Solved

min(timestamp) in select statement return null

Posted on 2006-07-01
1
1,177 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now