Require SQL Help for updating field - Need help ASAP - business issue :-(

pbare

Well Known Member
Hi All,
I am working on writing a SQL that will fix an incorrect Rate and Amount in F0618

I need to update WCMP (Number (9,2))

Current data in the field for an employee is 49516 - when we check the data we found that the data in this column was incorrect (our rate in RTWC was incorrect - it was 10 times to big)

When we view the data in Databrowser (using JDE 9.1) - we see 495.16 when it should actually be 49.516

I have written SQL to update the rate and the amount but when I run the sql - it is rounding the number off to 2 decimal places

Please see the attached pdf to see what I have been attempting to do...(it shows the data before and after as well as my SQL statement)

I am assuming that I am missing something since the first set of numbers is displaying the decimal place without it showing in the database

Any help would be greatly appreciated as I need to have this fixed by Monday at end of day
Thanks
Pam
 

Attachments

  • Issue with SQL.pdf
    145.9 KB · Views: 22

Larry_Jones

Legendary Poster
First off - do you understand how JDE stores numeric fields with decimals in a database? They don't write it with any decimal places. I can't tell if you understand this or not.

Second - not clear from your post what fields you're updating. You talked initially about WCMP, then RTWC, but your attachment shows you changed WCAM and RTWC. Maybe if you show us the exact SQL you used to update fields? Also - what database you use?
 

pbare

Well Known Member
Sorry - I attached a pdf document that shows the data that I was updating as well as the SQL that I was using.

I am updating WCAM and RTWC in F0618

I do understand a bit about how the data is stored which is why I asked the question. From databrowser, my new number looks correct but in the actual database it is showing the decimal - I am just not sure what I need to do to divide the original number by 10 to get the correct value and not have the decimal show in the Oracle database

My sql is showing a cast for the field that I am updating (WCAM) - the original line was WCAM/10 - I was trying to figure out a way to not have the decimal stored in the field.

I am using JDE 9.1 with Oracle 11G
 

JMR

VIP Member
When we view the data in Databrowser (using JDE 9.1) - we see 495.16 when it should actually be 49.516

Pam,

You can't do a straight-up SQL division on columns with zero decimals defined in the E1 data dictionary. Those numbers are stored in the database with zero decimals. The DD display decimals defines the implied number of decimal positions that the actual database column value has... for example: WCAM is (9,2). A DB value of 49516 is implied to be 495.16. Any SQL division you do (without properly truncating or rounding) will insert an actual decimal into the column value. This is very bad!

In the quote above, you mention the value should actually be 49.516. This is not a valid value for the given DD item! Do you mean it should be 49.52 (rounded to the nearest hundreth)? If so, you would need the database column to be: 4952

You need to perform a rounding (or truncating) of the values so they appear correct with the implied (display) number of decimals... ie: 49.52 should be stored as: 4952.
 
Discover how to build no-code data integrations and business process automations.

pbare

Well Known Member
Do you know what the SQL would look like for this? I know that the decimal is incorrect but I can't seem to get the query to do what I want it to so that it is correct.

Also, is there anyway that I can run a WCB report that would show the amounts from the pay period that I am fixing. I need to confirm that the change is working correctly.

Thanks
Pam
 

JMR

VIP Member
First of all, I'd get it back to where it was before you did the update. Something like cast(field*10 as numeric(9,0))... you need to get rid of that decimal.

Then getting the values how you want them will depend some on your particular situation. You will be losing some significance by essentially truncating the thousandths decimal position or rounding to the hundredths. This is business decision for your company. I am a bit confused by one of your comments above regarding the 459.16 should be 45.916 ... you need to realize you will not have that much precision.

I would first move the data to a temp table so you can play with the data without affecting your actual production table. Get the SQL right while testing on the temp data before you start fiddling with real data.

Try something along the lines of the following:
select 45916, ROUND(45916,-1), cast(ROUND(45916,-1)/10 as numeric(9,0)) as Rounded, cast(floor(45916)/10 as numeric (9,0)) as Trunc
 

JMast

Reputable Poster
So, if we smash the detailed and excellent responses from Jeremy and Larry together, the sql update could look like wcmp = round(wcmp/1000,2)*100. Example 49516 / 1000 = 49.516 rounded = 49.52 * 100 = 4952. As Jeremy stated, you will lose the detail when 1.6 becomes 2, but that would have happened with the correct rate anyway.
 
Top