Solved

Firebird DB - need to update column values

Posted on 2007-11-27
13
2,261 Views
Last Modified: 2013-12-09
Hello experts!

Due to a messy import of data from another database we need an SQL statement with Firebird syntax that will update the numeric values in entire columns of specific tables by rounding to two decimal places. I am using Database Workbench to access the database, but don't know the proper SQL command / script to run to adjust the values as stated above.  I also know that there are a probably a dozen post here that could help me, but I don't have time to keep looking for them.

I have table called JOBS, with columns called COST, AVGCOST, ESTCOST to name a few.  

TIA!
0
Comment
Question by:boris711
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 20363626
update jobs set cost = cast(cost as decimal(15,2)), ....... where

please check before doing on the live data that you get the rounding you want
0
 
LVL 7

Expert Comment

by:DanSo1
ID: 20372835
Sorry NickUpsons but your command will not round but just cut numbers...
For example:   1.566 will become 1.56
I think that boris711 need to round numbers so he shoud get 1.57
But I think the solution will need special UDF.

Regards
  Daniel

0
 
LVL 19

Expert Comment

by:NickUpson
ID: 20374012
if so you can fix by using
update jobs set cost = cast((cost + 0.005) as decimal(15,2)), ....... where
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:boris711
ID: 20397814
Thank you for your response, I will test this command.
0
 
LVL 7

Expert Comment

by:DanSo1
ID: 20401732
One more thing.
When "cost" will be negative you need to subtract those 0.0005
Like that:
   cast((cost - 0.005) as decimal(15,2))  .... where cost <0
0
 
LVL 1

Author Comment

by:boris711
ID: 20404533
It would appear to me that the above recommendations would change the datatype, maybe I'm wrong. It just seems like this should be easier.

Just to clarify, I am new to SQL and am having problems finding adequate (newbie) reference for the Firebird (we are using v1.5, not by our choice) flavor in particular. I own "The Firebird Book" but have found it most useful as a doorstop, probably due to my current level of knowledge.

I believe this SQL statement would do what I require in other SQL flavors:
Update ITEM Set avgcost = Round(avgcost, 4)

But in Firebird it would appear that there is no Round function. Can anyone tell me what the equivalent FB command would be?
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 20405450
so what exactly does this function do, round up, down, apply bankers rounding rules or .....
0
 
LVL 1

Author Comment

by:boris711
ID: 20405551
I believe this function will round to four decimal places all the values in column (avgcost) in table (ITEM). I admit, I could be wrong, but that's why I am asking the question in the first place.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 20405711
you need to decide exactly what rounding you want, e.g. 3.12345 - do you want that as 3.1234 or 3.1235
0
 
LVL 1

Author Comment

by:boris711
ID: 20405796
the typical rounding...3.12345 to 3.1235
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 total points
ID: 20406644
then this should do it

update jobs set cost = cast((cost + 0.00005) as decimal(15,4)), ....... where cost > 0.00
0
 
LVL 1

Author Comment

by:boris711
ID: 20429218
Thx, Nick, will give it a try on my test db.
0
 
LVL 1

Author Comment

by:boris711
ID: 20445041
For anyone else that might need it, this is the Firebird SQL command as run from within the Database Workbench SQL Editor. It worked exactly as required, performing standard rounding. If = or > 5 then round up, else round down.  

update <table_name> set <column_name> = cast (<column_name> as decimal(15,< #_dec_places>)) where <column_name> > 0.00;

It did not change the datatype of the column, as it appears it might. Also, if you remove "where <column_name> > 0.00" it works correctly on negative numbers.

Thanks for your time, Nick.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Displaying images within Access Form 6 74
Database Availability Group Distribution 9 49
sql views 3 55
Performance Issue in Oracle 3 45
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question