Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

min(timestamp) in select statement return null

Posted on 2006-07-01
1
Medium Priority
?
1,223 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 2000 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

885 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