1

SQL Rookie here. Using DB2.

I have a table People with attributes FirstName VARCHAR(20), Salary REAL and some others. I want to query with SELECT FirstName, Salary FROM People and receive this as the output:

FirstName    Salary
James        1000

but instead I get Salary in E Notation (because it was created as REAL):

FirstName    Salary
James        +1.00000E+003

How do I format the query to convert the values in Salary as numeric?

I tried using SELECT INTEGER(Salary) but it changes the Salary attribute header in the output to 2.

RSB
  • 11
  • 2
  • Does `select INTEGER(salary) as salary` work? – Gordon Linoff Feb 16 '14 at 05:15
  • @Gordon, please put your answer in the answers section. – AngocA Feb 16 '14 at 10:38
  • I hope this isn't a production database - [You shouldn't be using floating point to store money](http://stackoverflow.com/questions/273371/real-vs-floating-point-vs-money) (also read the linked entry in the top answer). Among other things, the (decimal) value `.1` **cannot** be accurately represented (how far away depends on a multitude of factors), so results can be surprising... Yes, most cases of fractional math will have rounding, but accountants have _very_ specific rules for this... – Clockwork-Muse Feb 18 '14 at 13:20

1 Answers1

2

Your query:

SELECT FirstName, INTEGER(Salary)
FROM People;

Does not assign a name to the second column. You assign a name using as:

SELECT FirstName, INTEGER(Salary) as Salary
FROM People;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786