I have a question, I want to format all the columns in the output of my selection not one by one
for example here is what i want to do:
this is my normal output:
name1 name 2 salary1 salary2 salary3 finances manager senior_manager
---- ------ ------- ------ ------- -------- ------- -------------
3000 400000 8000000 90 99999 500000 4000000 900000000000
1000 100000 9000000 90 100000 900000 1000000 800000000000
4000 300000 7000000 90 99999 400200 1000000 500000000000
this is what i want to do:
name1 name 2 salary1 salary2 salary3 finances manager employee4
---- ------ ------- ------ ------- -------- ------- -------------
3,000 40,0000 8,000,000 90 99,999 500,000 4,000,000 9,000
1,000 10,0000 9,000,000 90 100,000 900,000 1,000,000 8,000
4,000 30,0000 7,000,000 90 99,999 400,200 1,000,000 5,000
So the solution is;
select name1, name2 , salary1, salary2, salary3, finances, manager, employee4 from database_table
col name1 format 999,999.99
col name2 format 999,999.99
col salary1 format 999,999.99
col Salary2 format 999,999.99
col Salary3 format 999,999.99
col Finances format 999,999.99
col Manager format 999,999.99
col Emlpoyee4 format 999,999.99
order by 1;
What i want to do: col * format 999,999.99 or col all_columns format 999,999.99?
my question is, how to select all columns instead of one by one?
As you use SQL*Plus, then yes - there is a way to do it - use SET NUMFORMAT.
For example:
Before:
SQL> with test as
2 (select 3000 name1, 400000 name2, 8000000 salary1, 90 salary2, 9999 salary3 from dual)
3 select * from test;
NAME1 NAME2 SALARY1 SALARY2 SALARY3
---------- ---------- ---------- ---------- ----------
3000 400000 8000000 90 9999
Set:
SQL> set numformat 999G999G990
After:
SQL> /
NAME1 NAME2 SALARY1 SALARY2 SALARY3
------------ ------------ ------------ ------------ ------------
3,000 400,000 8,000,000 90 9,999
SQL>