Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: Problem in where clause

    I have a table with the below data.
    Longitude (Number(21,18)
    55,4527875455869
    55,4526677391391

    The below query works but when i compare the value of the column longitude in where clause it does not return any rows.

    select round(longitude,length(longitude)) from v_address_product (Works)

    select * from v_address_product
    where (round(longitude,length(longitude))='5545278754558 69' (Does not return any rows)

    Please help.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Why is there a space in that number? And for that matter, why is it a string?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What's the point in that ROUND function you used?
    Code:
    SQL> with test as (select 12.330 col from dual)
      2  select col,
      3         length(col),
      4         round(col, length(col))
      5  from test;
    
           COL LENGTH(COL) ROUND(COL,LENGTH(COL))
    ---------- ----------- ----------------------
         12,33           5                  12,33
    
    SQL>

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Additionally (comparing number with string, useless ROUND usage): as the NUMBER(21,18) data type contains 18 decimal digits and you posted only 13 ones, I suppose that the value you posted is not its full content - whichever client you used for obtaining it just truncated its value to some fixed width. Just see the following demonstration:
    Code:
    SQL> select cast( 55.452787545586901234 as number(21,18) ) c from dual;
    
             C
    ----------
    55.4527875
    
    SQL> column c format 00D000000000000000000
    SQL> select cast( 55.452787545586901234 as number(21,18) ) c from dual;
    
                         C
    ----------------------
     55.452787545586901234
    
    SQL> with t as ( select cast( 55.452787545586901234 as number(21,18) ) c from dual )
      2  select * from t where c = 55.452787545586901234;
    
                         C
    ----------------------
     55.452787545586901234

Posting Permissions

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