oracle procedure returning incorrect result

Posted on 2007-08-02
Last Modified: 2013-12-19
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.
Question by:alphamn
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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?

    Author Comment

    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)
    LVL 8

    Expert Comment

    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.
    LVL 8

    Expert Comment

    Forget the last one I didn't see your last comment. Can you post the function's code and how you call it?

    Author Comment

    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


    Author Comment

    i am calling the function
    x number;
    select countRecords(1186079100) into x from dual  ;
     and even tried  select countRecords(1186079100)  from dual  ;
    LVL 8

    Accepted Solution

    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(


    Author Comment

    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 .


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now