[Zope-DB] Sybase ASA, ZODBC and sum() expression in query

Maciej Wisniowski maciej.wisniowski@euro.coig.pl
Thu, 26 Jun 2003 08:28:27 +0200


I have a problem with Sybase Adaptive Server Anywhere 8 database and ZODBC
Database Connection  (ZODBCDA product version: 3.0.3).

I have a table 'testSumy':
create  table DBA.testSumy
(
    col1                 integer,
    col2                 integer,
    col3                 integer,
    id                   integer                        not null default
autoincrement,
    miesiac              integer,
    primary key (id)
)

with these data:

col1,col2,col3,id,miesiac <-- column names

23,231,2,1,2
2,1,2,2,2
2,1,64,3,3
3,134,64,4,3
3,,64,5,3

I've used these sqls to fill the table:
insert into testSumy(col1,col2,col3,miesiac) values(23,231,2,2)
insert into testSumy(col1,col2,col3,miesiac) values(2,1,2,2)Z
insert into testSumy(col1,col2,col3,miesiac) values(2,1,64,3)
insert into testSumy(col1,col2,col3,miesiac) values(3,134,64,3)
insert into testSumy(col1,col3,miesiac) values(3,64,3)


Notice the Null in the last row in the col2 column.

Now I'm trying this query:
          'select sum(col1), sum(col2), sum(col3) from testSumy group by
miesiac'

It works OK in Sybase's ISQL, but when run from Zope ZSQL Method it returns
nothing... I found it is caused by 'Null' in the last row (check: 'select
sum(col1), sum(col3) from testSumy group by miesiac'), but why? What to do?
I've tried using a view or even a stored procedure in database, but effect
is still the same - it works in ISQL, but returns nothing in Zope ZSQL
Method (Test). I tried using 'if then else' expressions in the query but
without any effect too... Any ideas? And one more thing - other queries are
working...

Maciej Wisniowski