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 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 ?
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.
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.