Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2294
  • Last Modified:

Firebird DB - need to update column values

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
boris711
Asked:
boris711
  • 6
  • 5
  • 2
1 Solution
 
NickUpsonSenior Network EngineerCommented:
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
 
DanSo1Commented:
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
 
NickUpsonSenior Network EngineerCommented:
if so you can fix by using
update jobs set cost = cast((cost + 0.005) as decimal(15,2)), ....... where
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
boris711Author Commented:
Thank you for your response, I will test this command.
0
 
DanSo1Commented:
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
 
boris711Author Commented:
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
 
NickUpsonSenior Network EngineerCommented:
so what exactly does this function do, round up, down, apply bankers rounding rules or .....
0
 
boris711Author Commented:
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
 
NickUpsonSenior Network EngineerCommented:
you need to decide exactly what rounding you want, e.g. 3.12345 - do you want that as 3.1234 or 3.1235
0
 
boris711Author Commented:
the typical rounding...3.12345 to 3.1235
0
 
NickUpsonSenior Network EngineerCommented:
then this should do it

update jobs set cost = cast((cost + 0.00005) as decimal(15,4)), ....... where cost > 0.00
0
 
boris711Author Commented:
Thx, Nick, will give it a try on my test db.
0
 
boris711Author Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now