That's great Denes and good luck with your app :)
On Monday, 14 September 2020 at 15:46:55 UTC+1 DenesL wrote:
> 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.
>
>
>
> On Friday, September 11, 2020 at 11:26:51 AM UTC-4 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/0d953675-cd55-4e0e-a130-8b539bbd87d1n%40googlegroups.com.
