Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Unanswered: select statement error

    I have a select statement that returns data sometimes and sometimes it returns no rows even though the table contains data. The database is 10g. Does anyone know how to fix this or has anyonn has this issue before. Thank you.

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    The problem is too vague for anyone to even attempt an answer.

    Give us the select statement. Also let us know if you are connecting as two different users, etc.

    For the database to behave in such a manner, the two tests (returning data and not returning data) must have some difference.

    Ravi

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I have psychic powers!
    After speaking with the powers-that-be I have deduced you are using
    an equals for a NULL argument.

    ie: where column1 = NULL

    should be: where column1 IS NULL
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2004
    Posts
    4
    Quote Originally Posted by rajiravi
    The problem is too vague for anyone to even attempt an answer.

    Give us the select statement. Also let us know if you are connecting as two different users, etc.

    For the database to behave in such a manner, the two tests (returning data and not returning data) must have some difference.

    Ravi
    The select statement is something like this.

    select * from TABLE1 where DATE1<>DATE2

    There are columns in the table where DATE1 does NOT EQUAL DATE2, I have seen these columns when data was returned. Sometimes it returns rows where the two dates don't match when I run the select, which it should. Other times it returns no rows. Also the data type for the 2 date field are the same.

  5. #5
    Join Date
    Oct 2004
    Posts
    145
    Is the records being updated ?

    Are you sure you are running the query on same data?

    How about truncating the date1 and date2 to date only. By default it includes time and if your records change, this maybe the root cause.

    Oracle will NOT return two different results for same query on same (static) set of data. Else they would have been out of business long ago ^.^

  6. #6
    Join Date
    Sep 2004
    Posts
    4
    Quote Originally Posted by JimYoo
    Is the records being updated ? Nope

    Are you sure you are running the query on same data? Yep

    How about truncating the date1 and date2 to date only. By default it includes time and if your records change, this maybe the root cause.

    Will Do, will let you know if I have the same issues

    Oracle will NOT return two different results for same query on same (static) set of data. Else they would have been out of business long ago ^.^

    Agreed, this is a very wierd problem.
    Is the records being updated ? Nope

    Are you sure you are running the query on same data? Yep

    How about truncating the date1 and date2 to date only. By default it includes time and if your records change, this maybe the root cause.

    Will Do, will let you know if I have the same issues

    Oracle will NOT return two different results for same query on same (static) set of data. Else they would have been out of business long ago ^.^

    Agreed, this is a very wierd problem.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    please post the results from these queries:
    PHP Code:
    select count(*) from table1;
    select count(*) from table1 where date1 <> date2;
    select count(*) from table1 where date1 date2;

    select count(*) from table1 where date1 is null;
    select count(*) from table1 where date2 is null;


    select 
      to_char
    (date1'mmddyyy hh24:mi:ss'date1,
      
    to_char(date2'mmddyyy hh24:mi:ss'date2
    from table1
    where date1 
    <> date2
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Sep 2004
    Posts
    4
    Quote Originally Posted by The_Duck
    please post the results from these queries:
    PHP Code:
    select count(*) from table1;
    select count(*) from table1 where date1 <> date2;
    select count(*) from table1 where date1 date2;

    select count(*) from table1 where date1 is null;
    select count(*) from table1 where date2 is null;


    select 
      to_char
    (date1'mmddyyy hh24:mi:ss'date1,
      
    to_char(date2'mmddyyy hh24:mi:ss'date2
    from table1
    where date1 
    <> date2

    Still testing, will let you guys know as soon as I have concrete evidence that this actually works. So far so good it's pulling back data, which is what its supposed to do. It might still fail though, keeping my fingers crossed.

  9. #9
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    No, u shouldn't be finger crossed.
    By all ur posts it feels that u believe Oracle is quite uncertain...It's not the case at all.

    The cause would be NULL comparison Or,

    for date comparison, it is always advisable to trunc the dates & compare. If you do not need to check for time-part and normal application doesn't require time part normamlly except some on-line business etc.

    Hope u able to make this get through and hope that may help. But, i am personally looking how u hv solved u case and like to know the final query u used where u r getting data always...

    Regards,
    Kamesh Rastogi
    - KR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •