CODEWITHSUNDEEP
oracle-databaseoracle10g
user767334
user767334

Reputation: 21

oracle 10g , query format

I have a small doubt. I have below query

SELECT empno 
        || '|' 
        || ename 
        || '|' 
        || sal 
        || '|' 
        || comm 
FROM   (SELECT empno, 
               ename, 
               sal, 
               comm 
        FROM   emp);

the output is coming as :

7611|Grp Fract|2001|.11
7499|ALLEN WOR|1600|.22
7521|WARD|1250|10.23
7566|JONES|2975|234.23
7654|MARTIN|1250|.98

the last column COMM has value as 0.11, 0.22, 0.98

but the above query returns data as .11,.22,.98. Can anyone help me understanding why it is happening when I am concatenating the data and how to resolve this , I need exact value as it is in COMM column.

The o/p should be as

7611|Grp Fract|2001|0.11
7499|ALLEN WOR|1600|0.22
7521|WARD|1250|10.23
7566|JONES|2975|234.23
7654|MARTIN|1250|0.98

Ths comm column is defined as number(7,2).

Thanks

Upvotes: 2

Views: 220

Answers (2)

user2114704
user2114704

Reputation: 1

The LTRIM(TO_CHAR(comm,'999990.99')) works for values which have 2 digits after decimal point. If value like 0.123523 is used above it rounds off the decimal places keeping 2 digits only.

Upvotes: 0

Benoit
Benoit

Reputation: 79243

Use the TO_CHAR function with a proper format model. It seems you want LTRIM(TO_CHAR(comm,'999990.99')) here.

Upvotes: 7

Related Questions