alphamn
asked on
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.
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.
ASKER
the item ids that i have in the table are . no null value
7
9
6
8
10
3
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)
7
9
6
8
10
3
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)
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.
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?
ASKER
CREATE OR REPLACE function countRecords(
REQEPOCHDT IN VARCHAR2)
return NUMBER
iS
count NUMBER;
BEGIN
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
is
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
REQEPOCHDT IN VARCHAR2)
return NUMBER
iS
count NUMBER;
BEGIN
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
is
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
ASKER
i am calling the function
declare
x number;
begin
select countRecords(1186079100) into x from dual ;
dbms_output.put_line(x);
end;
and even tried select countRecords(1186079100) from dual ;
declare
x number;
begin
select countRecords(1186079100) into x from dual ;
dbms_output.put_line(x);
end;
and even tried select countRecords(1186079100) from dual ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 .
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 .
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?