Start a new topic

returning null values

[This topic is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Background: I am performing some basic calculations on a large set of data contained in an AS400 DB, which I am connecting to via odbc. The data contains millions of records and I am performing multiple calculations over multiple fields then summarizing by month. Problem: I am getting a "Data conversion or mapping error" that is prohibiting me from performing the summarizing calculations. I pulled one of the calculations out (a simple prorate) that I've been working with to try and isolate the issue. Here is the sql I'm using for this. with TranData (tdnrov, tdmnth, tdtestfld) as (select ennrov, substr(char(enacd5),4,2) as "Month", enargf * (enqto5/30) as "TranAlaCarteAmt" from wowlivfile.ccencpp join wowlivfile.ccekcpp on ennrov = eknrov and encnbr = ekcnbr and ennuo9 = eknuo9 and enacd5 = ekacd5 and enbgnn = ekbgnn and enbgrr = ekbgrr where ennrov = 1 and enacd5 between 1141101 and 1141231 and enqto5 NE 0 ) select distinct(tdnrov) as "Site", tdmnth as "Month", sum(tdtestfld) as "TtlTstFld" from TranData group by tdnrov, tdmnth order by tdnrov, tdmnth; field specs: ennrov num(3) enacd5 num(7) - a date string in CYYMMDD format enargf dec(7,2) - a monthly rate enqto5 num(5) - the number of days for the rate The num fields may be dec but they are all 0 decimal places. I'm not sure if that makes any difference. Thinking there may be an invalid value in one of the fields I did a test using a cross join where I took all of the values of enargf (ColumnA) and all the values of enqto5/30 (ColumnB) and multiplied them together (ColumnC). I then took the results and put them into a spreadsheet (attached). I also did the multiplication of the two values (ColumnD) in the spreadsheet. I filtered the results in the spreadsheet to only show the null values and notice that they are all rows where the product of VALUEA * VALUEB is greater then 3 digits. Can someone please explain to me what is happening? Edited by: Chris on Jan 14, 2015 8:51 PM

[This reply is migrated from our old forums. The original author name has been removed]

Re: returning null values
A followup discovery. When I update the sql and take out the "/30" part I don't get an error. Obviously, this logic is necessary and can't be excluded. with TranData (tdnrov, tdmnth, tdtestfld) as (select ennrov, substr(char(enacd5),4,2) as "Month", enargf * enqto5 as "TranAlaCarteAmt" from wowlivfile.ccencpp join wowlivfile.ccekcpp on ennrov = eknrov and encnbr = ekcnbr and ennuo9 = eknuo9 and enacd5 = ekacd5 and enbgnn = ekbgnn and enbgrr = ekbgrr where ennrov = 1 and enacd5 = 1141102 and enqto5 0 ) select distinct(tdnrov) as "Site", tdmnth as "Month", sum(tdtestfld) as "TtlTstFld" from TranData group by tdnrov, tdmnth order by tdnrov, tdmnth;
[This reply is migrated from our old forums.]

Re: returning null values
Chris, You will probably get a faster and accurate response on your question by checking with the AS/400 community. This forum is primarily for DbVisualizer related discussions. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: returning null values
I wasn't sure if this was an sql problem or a DbVis problem.