oracle procedure returning incorrect result

i am trying to get record count between two dates
when i run the statement written below   it gives me record count as 6  --- correct out put

SELECT COUNT(item_id) FROM tbl_items  
WHERE item_id IN (SELECT  distinct item_id FROM TBL_transactions
WHERE start_dt < 1186079100 AND  end_dt >=1186079100);

where as when i include the same query in a procedure / function it returns record count as 7.

Please note the start_dt and end_dt have time in epoch format.

Can any one guide me why is this happening.

thanks in advance.
Who is Participating?
I guess that  tbl_auctions is the same as TBL_transactions in your first post.

The parameter in the procedure is VARCHAR2. That means that within the proc, start_dt and end_dt are converted to_char and then compared as strings with REQEPOCHDT.

When you run the statement select * from tbl_auctions where start_dt < 1186079100 and end_dt >= 1186079100
you use number.

I don't now the datatype of start_dt and  end_dt , nor the values of the data in your table but this could be the problem, so try declaring the function as
CREATE OR REPLACE function countRecords(

Guy Hengel [angelIII / a3]Billing EngineerCommented:
WHERE item_id IN (SELECT  distinct item_id
is double work, you don't need the distinct:
WHERE item_id IN (SELECT item_id

now, do you have NULL values in item_id?
alphamnAuthor Commented:
the item ids that i have in the table are . no null value

if i hardcode the value for v_start_dt and v_end_dt  in the procedure it returns correct record  count  (i.e 6 records)but when i am passing as input value it is returning me incorrect record count (7 records)
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Can you post the code of the procedure? I suppose you don't use hardcoded values within the procedure. If that is the case maybe youre just missing something, a second look could help.

If the code is identical, the only logical explanation I can think of, is the following:
If when you run the query you connect as UserA (and reference UserA schema) and the function or the procedure is created in schema UserB, and the tables exist in both schemas, then it is possible that they have different data.
Forget the last one I didn't see your last comment. Can you post the function's code and how you call it?
alphamnAuthor Commented:
CREATE OR REPLACE function countRecords(
return  NUMBER
            count NUMBER;
SELECT COUNT(item_id) INTO count FROM tbl_items  
  WHERE item_id IN (select item_id from tbl_auctions where start_dt < REQEPOCHDT and end_dt >= REQEPOCHDT);
  return  count;
   END countRecords;

the output i get on executing the SQL --- select * from tbl_auctions where v_start_dt < 1186079100 and v_end_dt >= 1186079100
AUCTION_ID      ITEM_ID        START_DT         END_DT            STATUS
    14                         7         1184667331       1186079100            0
    16                        9          1183452345             1186079100            0
    13                        6          1183766253              1186079100              1      
    15                        8         1185622452            1186079100              0
    17                     10        1183033660            1186079100            1      
    6                     3           1182498883              1192900000            0

alphamnAuthor Commented:
i am calling the function
x number;
select countRecords(1186079100) into x from dual  ;
 and even tried  select countRecords(1186079100)  from dual  ;
alphamnAuthor Commented:
thanks for the help...
the data type of start_dt and end_dt is varchar2 . hence the comparison was not happening properly now to compare the two values i  converted  it to number  TO_NUMBER() . Now i am getting correct results .

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.