Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    58

    Unanswered: View behaving oddly

    EDIT: NEVERMIND I JUST WASNT PAYING ATTENTION...PLEASE DISRESPECT

    Hi all,
    I have a views called IMSTOCK with the following columns
    SQL> desc imstock
    Name Null? Type
    ------------------------------- -------- ----
    ITEM NOT NULL VARCHAR2(24)
    DES VARCHAR2(30)
    LOC NOT NULL VARCHAR2(3)
    QOH NUMBER
    Q_ALLOCATED NUMBER
    ONORDER NUMBER
    SAFETY_STOCK NUMBER
    PRODCAT VARCHAR2(4)
    AVAILABLE NUMBER
    EST_SALES NUMBER
    LEADTIME NUMBER
    ORDER_CYCLE NUMBER
    AVG_UNIT_COST NUMBER(12,4)
    COMMENTS2 VARCHAR2(30)

    SQL> select count(*) from imstock
    2 ;

    COUNT(*)
    ---------
    1151

    HERE IS THE MYSTERY:

    SQL> select count(*)
    2 from imstock
    3 where QOH =0 and Q_allocated=0 and onorder=0;

    COUNT(*)
    ---------
    307
    BUT
    SQL> select item, loc, QOH, Q_allocated, onorder
    2 from imstock
    3 where QOH >0 and Q_allocated >0 and onorder >0;

    no rows selected

    SQL> select item, loc, QOH, Q_allocated, onorder
    2 from imstock
    3 where QOH<>0 and Q_allocated<>0 and onorder<>0;

    no rows selected

    Now I know that tuples where the condition holds true do exist like:
    SQL> select item, loc, QOH, Q_allocated, onorder
    2 from imstock
    3 where item='BFIL';

    ITEM LOC QOH Q_ALLOCATED ONORDER
    ------------------------ --- --------- ----------- ---------
    BFIL 1 79 1 0
    BFIL 3 0 0 0
    BFIL 4 1 0 0
    BFIL 5 19 0 0
    BFIL 9 0 0 0 <---------Like this one

    Why is my query returning 0 rows? Does t have something to do with the fact that the datatype is NUMBER and not in the form NUMBER(p,s)?? Please help.
    Last edited by da_coolestofall; 10-14-04 at 20:44.

Posting Permissions

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