Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

min(timestamp) in select statement return null

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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo 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.
Suggested Courses

564 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