Database Administrator, Informix SQL
I am using Informaker 6.0 DataBase Administrator.
I am using two tables and they are joined with a full joins.
I need the query to 'sum' a field_P which begins with a zero for records
written in a particular month.
First, I built the query using Infomaker 6.0 SQL and the query returns the
correct counts, but when I copy the query over to the database Administrator
the query doesn't run. Can you tell me why the query would work in one place
and not the other? Aren't the queries transportable from one software suite
to another?
Informaker Report Query:
SELECT count( distinct TableA.jo_n),
sum(TableA.pos +0 )
FROM TableB.ers,
TableA
WHERE ( TableA.erid = TableB.erid ) and
( TableA.date >= ('1998-5-1 00:00:00') ) and
( TableB.sic like '0%')
Informaker DataBase Administrator query:
update tableA
set column2 =
(select sum(field_A + 0)
from tableA A, tableB B
where (tableB_id = tableA_id)
and (A.date >= ('1998-5-1 00:00:00'))
and (B.code[1,1] in ("0")))
where fo_id ='S'and row_id = 'CAxxxxxxx';
How can I use "Distinct" so that it sums only distinct records for table A?
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum