number format in toad

hi
sometimes i would like to export the result of my query from toad to ms excell
there , i have to every time to format the numbers , where always coming as currency format
i used those 2 formulas :
 to_char(cur_sls,'999,999,999') cur_sls

Open in new window

as number with no decimal places
and
to_char(100 * (PRV_SLS-PRV_EXP)/NULLIF(PRV_SLS,0) ,'fm9990.00')||'%'

Open in new window

to forrmat number as percentage with 2 dicimal places
it is ok in the result itself , but when exported to toad , i found that i need to again convert the strings to number
any way to direct convert as number
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
to_char converts your results to text

try round(x,2)  to convert your numerical value to 2 decimal places while still keeping it a number
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
and what about the percentage with 2 decimal places
0
sdstuberCommented:
that's what the "2" is for in the round


if you append the '%' at the end, then you're turning the value into text again.

If you want % to show up in the results, but still be numeric, then let Excel do the formatting for you
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NiceMan331Author Commented:
yes correct
but no way to format the number as : 999,999,999 as number instead of text ?
0
sdstuberCommented:
no,  commas aren't numeric, commas are text

like percentage, if you want non-numeric characters in the representation and it should still be considered numeric, then let Excel do the formatting for you
0
NiceMan331Author Commented:
ok , finally , and i don't know if this regards to toad or to excell it self
when i export to excell , and select format as comma , it becomes accounting format with currency symbol
any way to change it to instead of another step in excell
0
sdstuberCommented:
you're exporting text and excel is doing its best to interpret it.

I would export numbers only, and put all formatting on the excel side.
0
NiceMan331Author Commented:
No , I changed to number , then when I format in excell it comes like this
0
NiceMan331Author Commented:
i still have a problem with decimal
 (CASE WHEN grouping_level IN(1,3) THEN round(100 * CUR_SLS-CUR_EXP,2) ELSE NULL END) AS CUR_PROF,

Open in new window


in the data view of toad it displayed as 2.65
when exported to excell it comes  3
then i tried to formatted with comma in the toolbar of excell it comes 3 $
then should format cell to select no currency sympol with 2 decimal places to show again 2.65
should this posted as "excell" topic also ?
0
sdstuberCommented:
I am a topic advisor, I can add the excel topic if you want.

Did you set your numeric format to display 2 decimal places?

cell format
0
NiceMan331Author Commented:
yes , when doing formatting like this it is ok
but i want to save this step , because my query has alot of columns
and each column will have different format
in conclusion , i don't want to use formats of excel
0
sdstuberCommented:
>>>in conclusion , i don't want to use formats of excel

then you can't do what you want

Neither Toad nor Oracle has any control over how Excel will format the data.
0
NiceMan331Author Commented:
When I'm copying data from any source to excell , the numbers stay general number format , except toad , when I copied from it to excell it coming with current format
0
sdstuberCommented:
>>> copying data from any source to excell , the numbers stay general number format

do that then.

copy to excel instead of creating a new file.

Don't export as "Excel File"  instead start Excel and then choose export as "Excel Instance"

When I do File, 2.65 it is formatted as 3,  when I export to an Instance it is formated as 2.65
0
sdstuberCommented:
one last thing,  are you choosing the "general format"  when you export?
When I do that, the excel file works,  if it doesn't for you, then try the Instance method.

or, you may have to contact Dell and put in a request for a new Toad feature


export
0
NiceMan331Author Commented:
I'm not sure I have this option , I will check it.  It may work , or , last question : any setting in the registry of oracle such as date format , is there any setting of numbers there ?
0
sdstuberCommented:
Those settings apply to your NLS_NUMERIC_CHARACTERS setting which controls your grouping and decimal characters.  (In the US  commas between thousands and millions and a period to indicate the decimal point.)

Plus, even if there was a formatting setting, that would mean the your numbers would be converted to text in order to satisfy the format.  So you wouldn't want to use such a thing even if it did exist.
0
NiceMan331Author Commented:
So I will check that option in my toad
0
sdstuberCommented:
I'm using 12.7.1.11.

I don't know what version added the "general" option.  
I think it's been there for a while but I honestly don't use it much so I could be wrong.
0
NiceMan331Author Commented:
the option is there , but still same issue
i think excell option , if you don't mind , could you added to excell topic
by the way , i just see the report section of toad , it seemed wonderfull tools
i want to learn about it
it will save a lot of time of exporting to excell
how i can learn about it ?
0
sdstuberCommented:
it's unlikely an Excel person can tell you how to get toad to send data differently.

If toad created the file, then excel can't do anyting because toad is in control.

Once the data is in toad, you already know how to format it.

If you use the export to excel instance option you might have some options with vba that would detect the new worksheet being added and format the data for you.

But, both the general option on file create and export to instance work for me.
If toad isn't doing what you want you will probably need to talk to Dell.
The toad staff is very responsive.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.