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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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.
0
alphamn
Asked:
alphamn
  • 4
  • 3
1 Solution
 
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?
0
 
alphamnAuthor Commented:
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)
0
 
YANN0SCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
YANN0SCommented:
Forget the last one I didn't see your last comment. Can you post the function's code and how you call it?
0
 
alphamnAuthor Commented:
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



0
 
alphamnAuthor Commented:
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  ;
0
 
YANN0SCommented:
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(
REQEPOCHDT IN  NUMBER)

0
 
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 .


0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now