Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    35

    Unanswered: Oracle query says field value 1 does not equal 1 ?!

    I have a table that I'm running a query on, there are two data fields that are the same data type. Let's call them Field1 and Field2.

    In my query I can try something like
    Code:
    decode(Field1,Field2,'TRUE','FALSE')
    and it will say 'FALSE' even if Field1=1 and Field2=1, or Field1=2 and Field2=2

    Or if I try to do a where condition like
    Code:
    Field1=Field2
    it doesn't match up the ones where they are both the same value.

    Does anyone know what might be causing this?

    I've tried stuff like
    Code:
    to_number(Field1)=to_number(Field2)
    --or--
    to_char(Field1)=to_char(Field2)
    --or--
    to_char(Field1)||'0'=to_char(Field2)||'0'
    and it's always the same result.. even if the field has the same value as each other it doesn't match in the equation.

    How can I get around this?

    Thanks for any tips!

    Oh yeah and it's Oracle 9.2

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If the datatype is varchar ... have you tried to LENGTH(FIELD1) ...
    LENGTH(FIELD2) just to be sure there is not a space or unprintable character ???

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi,

    Here is my test.

    --======================

    SQL> desc test
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    FIELD1 VARCHAR2(10)
    FIELD2 VARCHAR2(10)

    SQL> select * from test;

    FIELD1 FIELD2
    ---------- ----------
    1 1
    1 2

    SQL> select DECODE (field1, field2, TRUE, FALSE)
    2 FROM test;
    select DECODE (field1, field2, TRUE, FALSE)
    *
    ERROR at line 1:
    ORA-00904: "FALSE": invalid identifier


    SQL> select DECODE (field1, field2, 'TRUE', 'FALSE')
    2 FROM test;

    DECOD
    -----
    TRUE
    FALSE

    --==================================================

    I guess, You should have all the datatypes matched. If field1 and field2 are number and 'TRUE' AND 'FALSE' are VARCHAR then it should not work.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    May 2004
    Posts
    35
    Both fields are number and both are identical. From view ALL_TAB_COLUMNS --

    DATA_TYPE = NUMBER
    DATA_LENGTH = 22
    DATA_PERCISION = 7
    DATA_SCALE = 0

    What do you think now?

  5. #5
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Quote Originally Posted by joshg
    Both fields are number and both are identical.
    That is the problem.

    (1)try to convert both of them in VARCHAR2 then it will work

    select DECODE (field1, field2, 'TRUE', 'FALSE')
    where field1 and field2 are of datatype VARCHAR2.

    OR

    (1) try to use 1 FOR TRUE AND 0 FOR FALSE (in this case 1 and 0 are NUMBERS)

    i.e, DECODE (field1, field2, 'TRUE', 'FALSE') can be replaced by
    DECODE (field1, field2, 1, 0).

    You have not observed my test precisely.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  6. #6
    Join Date
    May 2004
    Posts
    35
    Well I understood your test and I even tried to apply it however I got the same result.

    In fact if I do basically exactly what you have done, DECODE(Field1,Field2,1,0) it is returning 0 every time even if they are both the same number (e.g. 1 and 1).

    Also if instead I do a DECODE(to_char(Field1),to_char(Field2),'1','0') it returns '0' for everything...

    So now what do you think?

  7. #7
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Could you please post one or two rows of data and the actual select statement that you used? A DDL statement for the table along with the insert statements for at least 1 row of data would be useful.


    Ravi

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    Are you sure there aren't trailing decimals that you're not seeing. When you select in sql*plus, numbers are formatted to display in 10 positions (1 pos for the +/-, leaving 9 for the number):

    SQL> select .99999999999999999999 from dual;

    .99999999999999999999
    ---------------------
    1

    SQL> select 1.0000000000000001 from dual;

    1.0000000000000001
    ------------------
    1


    So try "set numwidth 50", and then check your data to see if you might have a decimal you don't know about.

  9. #9
    Join Date
    Apr 2004
    Posts
    35

    precision error

    i think the error is due to the fields might be calculated one like division . therefore unseen decimal like 1.0000001 might be inserted please check for it
    by displaying thru
    column field1 format 99.999999999
    column field2 format 99.999999999
    select field1,field2 from table;

    skg
    ocp 9i

Posting Permissions

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