Results 1 to 7 of 7

Thread: query

  1. #1
    Join Date
    Feb 2009
    Posts
    48

    Unanswered: query

    select sal||'add sal'||sal+100 from emp;
    what is the problem with this query.


    thanks
    Take risks: if you win, you will be happy; if you lose, you will be wise.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It (your query) is confused about whether it is dealing with strings (that can be concatenated) or numbers (that can be added). Since it doesn't specify, Oracle has guessed:
    Code:
    select to_number(sal||'add sal'||sal)+100 from emp;
    which obviously fails, whereas what you probably meant was:
    Code:
    select sal||'add sal'||to_char(sal+100) from emp;

  3. #3
    Join Date
    Feb 2009
    Posts
    48
    but why it fails when i change the operator + with * , then it run correctly but again if i change the with - again giving me the error why?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by unforgotton_man View Post
    but why it fails when i change the operator + with * , then it run correctly but again if i change the with - again giving me the error why?
    I am sorry to see CUT & PASTE are broken for you.
    Speaking only for myself, I would like to see your actual SQL & exactly how Oracle responds.

    You report "error".
    By last count Oracle throws more than 20,000 different error codes.
    Trying to debug SQL we can not see to fix unknown errors is task that I can not accomplish.

    Realize that even after you post SQL, unless & until we have tables & test data we will NOT be able to run the SQL ourselves.

    You need to help us so we can help you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by unforgotton_man View Post
    but why it fails when i change the operator + with * , then it run correctly but again if i change the with - again giving me the error why?
    Operator precedence, * has greater than both || and + operators.
    Anyway, as pointed by andrewst, it is not good to rely on implicit conversion. As you can see, it is less readable and much harder to understand (as you demonstrated).

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by unforgotton_man View Post
    but why it fails when i change the operator + with * , then it run correctly but again if i change the with - again giving me the error why?
    Because the * operator has a higher precedence than || whereas as + has a lower precedence. So:
    Code:
    select sal||'add sal'||sal*100 from emp;
    is treated as
    Code:
    select sal||'add sal'||to_char(sal*100) from emp;
    but
    Code:
    select sal||'add sal'||sal+100 from emp;
    is treated as
    Code:
    select to_number(sal||'add sal'||sal)+100 from emp;
    Solution: be explicit about what you want to do - i.e. write:
    Code:
    select sal||'add sal'||to_char(sal+100) from emp;
    Did you not learn at school that 1+(2*3) and (1+2)*3 are not the same thing?

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by andrewst View Post
    Did you not learn at school that 1+(2*3) and (1+2)*3 are not the same thing?
    yes, most of your questions are basic "Read the documentation" and "understanding mathematics" types of questions/answers. It sounds like you just installed oracle without reading any of the documentation on what you are doing and why.

    By cut/paste Ana is referring to copying EXACTLY the code and output from sqlplus that you are running and pasting the code into this forum. Saying "I get an error" is impossible for us to decipher. We need the oracle (ORA-XXXXX) error code, etc.

    example:
    Code:
    the_duck@db01> select 1+(2*3) total_1,  (1+2)*3 total_2 from dual;
    
       TOTAL_1    TOTAL_2
    ---------- ----------
             7          9
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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