Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: Help needed in - Decode statement

    Hi Folks,

    I have a decode statement DECODE(quality_flag, 'Q', '?',NULL,product_price, product_price). The above decode statement results the product price in character format. I would like to have the result in number format. Friends, help me in resolving this problem.

    Thanks,
    Kalai.

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Code:
    decode(quality_flag, 'Q', '?', NULL, to_number(product_price), to_number(product_price))
    If you want a specific number format, add that as a second argument to the to_number part.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Jan 2004
    Posts
    7

    doesn't work..

    Thanks for the reply.
    I tried as per your suggestion.But even then it returns the result in character and not in number.
    Could please help me further to arrive at the solution ?
    is there any alternative ?

    Thanks,
    Kalai.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Basically, this DECODE says that IF quality_flag = 'Q' THEN result = '?' OTHERWISE result = product_price

    Now, what does it mean, "the result is in character"? How do you know it is a character and not the number? Can you provide sample data? Result you have and the one you'd like to have?

  5. #5
    Join Date
    Jan 2004
    Posts
    7

    With sample data

    Basically, this DECODE says that IF quality_flag = 'Q' THEN result = '?' OTHERWISE result = product_price
    Yes you are right.

    //what does it mean, "the result is in character"? How do you know it is a character and not the number?
    Answer to your question is, when the values are dispalyed and say i'm exporting the result set to excel, the numbers are in character format.So i'm unable to plot the graph.
    Hope i have cleared your doubt.

    Could you please help me in this regard ?

    Thanks.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess you wrote this query, spooled output into a .txt (or similar) file and then tried to open it with Excel to draw a graph. I'd say that the main reason you didn't succeed is a decimal point character, isn't it? Your column value is, for example, "123,45" while Excel expects "123.45".

    What can you do about it? First solution is in the SQL environment - select TO_CHAR(decode(quality_flag, ...), '999.99') - this will result in a char output, but Excel will see it as a number.
    Or, leave it as it was, open the file in Excel, "select all" and do a "Replace" and replace "," with "." (or vice versa, depends on what you need).

    Why is that so? Regional settings on your PC are different from the NLS that is set up in your database. You could, as a third solution, make those settings equal to avoid such problems in future.

    But, if this story isn't correct, let us know; we'll find the solution, sooner or later.

Posting Permissions

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