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 _ On Friday, 11 September 2020 at 03:30:54 UTC+1 DenesL wrote: > 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 > > On Thursday, September 10, 2020 at 12:04:44 PM UTC-4 villas wrote: > >> 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 >> >> >> On Wednesday, 9 September 2020 at 15:31:59 UTC+1 DenesL wrote: >> >>> 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" >>> <http://10.0.0.27:8000/admin/default/edit/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" >>> <http://10.0.0.27:8000/admin/default/edit/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 >>> >> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/5c033d49-8cd1-46b7-aa86-fa75b078d2c8n%40googlegroups.com.
