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.
On Friday, 11 September 2020 at 16:26:51 UTC+1 DenesL wrote:
> 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
>
> On Friday, September 11, 2020 at 6:34:50 AM UTC-4 villas wrote:
>
>> 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/f254846f-0d4a-4d54-8ce9-e5816133cc7an%40googlegroups.com.
