SQL query sometimes return no value

Question

I have a table in my Oracle Database that has an ID and a date which represents periods of the year.

Now, i have a C# code that selects the last period of the table:

  public static Period Get_Last_Period()
    {
        DataRow dr = Get_DataRow_InLineSQL("SELECT * FROM PERIODS WHERE ROWNUM = 1 ORDER BY PERIOD_ID DESC");

        Period iPeriod = new Period(dr);
        return iPeriod; 
    }

The problem is that sometimes randomly it returns a "null" value for the DataRow. Any idea of why this could be happening?

I would guess that you are getting 0 rows returned. Is there "always" something returning with that ROWNUM = 1? or does the table get purged? There are so many factors that could be causing this. Need more info. — Nyra, Jun 06 '14 at 17:11

Thorsten Kettner · Answer 1 · 2014-06-06T20:29:37.707

You are selecting a random record from your table. WHERE ROWNUM = 1 as the only criteria tells the dbms just to select any record and stop there. Then you order this one record by period_id which has no effect, because one cannot change the sort order of one single row of course.

If sometimes the query returns a null record, it means there is a null record in your table and the dbms happens to pick this one. That's all.

If you want the sorting to happen first and then pick the row with the highest number, you should change your query to:

SELECT * FROM (SELECT * FROM PERIODS ORDER BY PERIOD_ID DESC) WHERE ROWNUM = 1

EDIT: As it is your C# code producing the null value, my answer above is not correct, but it may be related. What is Period? It is some class that has a constructor that gets a DataRow. As far as I know, a constructor can either fail, but then you would get an exception, or it produces an instance, but that would never be null. So: No, I don't know what happens here. Maybe above explanation ragrding the query helps you find out what your constructor does with the DataRow it gets.

SQL query sometimes return no value

1 Answers1