sum decimal error?

[DenesL]

Hi group

running Version 2.20.4-stable+timestamp.2020.05.03.05.18.50
with SQL Server 2012 on Win 8.1

I am getting the following error when I try to sum a decimal field in a table:

Traceback (most recent call last):
File "C:\w2p\web2py22004py3\gluon\restricted.py", line 219, in restricted
exec(ccode, environment)
File "C:/w2p/web2py22004py3/applications/scanpak/controllers/default.py", line 1948, in <module>
File "C:\w2p\web2py22004py3\gluon\globals.py", line 430, in <lambda>
self._caller = lambda f: f()
File "C:/w2p/web2py22004py3/applications/scanpak/controllers/default.py", line 1941, in scanvsdoc
ss = db(ti.DocNum == doc).select(ti.ItemCode, scnqtysum, groupby = ti.ItemCode)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\objects.py", line 2634, in select
return adapter.select(self.query, fields, attributes)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\adapters\base.py", line 874, in select
colnames, sql = self._select_wcols(query, fields, **attributes)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\adapters\base.py", line 768, in _select_wcols
query = self.expand(query, query_env=query_env)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\adapters\base.py", line 487, in _expand
rv = op(first, second, **optional_args)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\dialects\base.py", line 406, in eq
self.expand(second, first.type, query_env=query_env),
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\dialects\__init__.py", line 97, in expand
return self.adapter.expand(*args, **kwargs)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\adapters\base.py", line 497, in _expand
rv = self.represent(expression, field_type)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\adapters\base.py", line 430, in represent
return super(SQLAdapter, self).represent(obj, field_type)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\adapters\base.py", line 384, in represent
return self.representer.represent(obj, field_type)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\representers\__init__.py", line 246, in represent
rv = self.get_representer_for_type(field_type)(rv, field_type)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\representers\__init__.py", line 138, in __call__
return self.adapt(self.call(value, field_type))
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\representers\__init__.py", line 135, in _call
return self.inner_call(value)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\representers\__init__.py", line 123, in _inner_call
return self.obj.f(self.representer, value, **kwargs)
File "C:\w2p\web2py22004py3\gluon\packages\dal\pydal\representers\base.py", line 29, in _integer
return str(long(value))
TypeError: __int__ returned non-int (type NoneType)

the code looks like this:

  ti = db.itmscan
  scnqtysum = ti.scanqty.sum()
  ss = db(ti.DocNum == doc).select(ti.ItemCode, scnqtysum, groupby = ti.ItemCode)

and the table definition:

db.define_table('itmscan',
  Field('DocNum', 'integer'),
  Field('ItemCode', 'string', 20),
  Field('Dscription', 'string', 100),
  Field('box', 'integer', default=1),
  Field('scanqty', 'decimal(19,6)', default=0.0),
  Field('scanid', 'string'),
  Field('empid', 'string'),
  Field('status', 'string'),
  Field('spcins', 'string', default=''),
  Field('tstamp', 'datetime'),
)

I tried adding a represent to the field but it made no difference.

In the code I have a sum on another decimal field from a different table just a few lines before the failing one that works just fine. That field is also decimal(19,6).

Thanks for any help, if I have made a mistake I can't see it.

Denes

[villas]

Hi Denes

Just a thought,  and I'm not sure if this is the answer,  but the following indicates that there is a null value in the DB field:

TypeError: __int__ returned non-int (type NoneType)

Maybe you initially created the field without a default and then added the default=0.0 later.  This may have enabled you to create records with null values?

I therefore suggest you search for any null values and replace them with 0.0.  Perhaps you could run this query....

update itmscan set scanqty = 0.0 where scanqty is null

[DenesL]

Hi villas

thanks for your reply.

There should be no NULLs in there since I deleted all tables and started from a blank slate.

Still no idea why this happens. Why is trying to use __int__ if it is a decimal?.

Denes

[villas]

Are you sure your doc search value is an integer?

Maybe a little more experimentation.  Simplify and then incrementally add complexity.  Start here...

ss = db(ti.DocNum == 999999).select(scnqtysum)

Also, check the SQL:

ss = db(ti.DocNum == doc)._select(scnqtysum)  ## note the underline chr _

[DenesL]

Hi villas

thanks for your suggestion.

I ran a test in the shell and it works:

web2py Web Framework
Created by Massimo Di Pierro, Copyright 2007-2020
Version 2.20.4-stable+timestamp.2020.05.03.05.18.50
Database drivers available: sqlite3, pyodbc, imaplib, pymysql
WARNING:web2py:import IPython error; use default python shell
Python 3.7.8 (tags/v3.7.8:4b47a5b6ba, Jun 28 2020, 08:53:46) [MSC v.1916 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> db.tables
['auth_user', 'auth_group', 'auth_membership', 'auth_permission', 'auth_event', 'auth_cas', 'docscan', 'itmscan']
>>> ti=db.itmscan
>>> ss=db(ti.DocNum==350).select()
>>> print(ss)
itmscan.id,itmscan.DocNum,itmscan.ItemCode,itmscan.Dscription,itmscan.box,itmsca
n.scanqty,itmscan.scanid,itmscan.empid,itmscan.status,itmscan.spcins,itmscan.tstamp
1,350,P10002,"PC - 12x core, 64GB, 5 x 150GB SSD",1,1.000000,SCANNER1,emp003,,,2020-09-09 09:14:12
2,350,P10002,"PC - 12x core, 64GB, 5 x 150GB SSD",1,1.000000,SCANNER1,emp003,,,2020-09-09 09:15:25
>>> scnqtysum = ti.scanqty.sum()
>>> ss=db(ti.DocNum==350).select(scnqtysum)
>>> print(ss)
"SUM(""itmscan"".""scanqty"")"
2.000000

so something is amiss somewhere...

BTW, having DocNum=='350' makes no difference.

Denes

[villas]

Hi Denes

Now that you are making things work on the commandline,  you should be able to figure it out. 

Maybe you don't need a groupby. 

Maybe you have a non-integer value in one of the fields (yes, this kind of thing could happen on sqllite).  An integer saved as a string will be converted to an int,  but for example 'A1234' would clearly not work.  Your data could turn out to be the problem but problems and bugs can be DB specific too. 

Keep experimenting! 

Best wishes.

[DenesL]

I have found my mistake, in the query

db(ti.DocNum == doc)

I was using the wrong variable (doc) which is a row,

it should be docnum.

Thanks villas for your help and words of encouragement.

[villas]

That's great Denes and good luck with your app  :)