Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Database Administrator / Informix SQL

2 posts in PB Infomaker Report Writer Last posting was on 1998-07-02 18:27:33.0Z
progers2 Posted on 1998-06-30 23:49:46.0Z
Newsgroups: sybase.public.infomaker.general
Date: Tue, 30 Jun 1998 16:49:46 -0700
From: progers2@edd.ca.gov
X-Mailer: Mozilla 3.0 (Win95; I)
MIME-Version: 1.0
Subject: Database Administrator / Informix SQL
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 31
Message-ID: <348_3599799A.7D76@edd.ca.gov>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7029
Article PK: 243059

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?


Terry Dykstra [TeamPS] Posted on 1998-07-02 18:27:33.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra [TeamPS]" <no_spam_tdykstra@cfol.ab.ca>
Subject: Re: Database Administrator / Informix SQL
Date: Thu, 2 Jul 1998 12:27:33 -0600
Lines: 51
Organization: Canadian Forest Oil Ltd.
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_3Z6$#1dp9GA.220@forums.powersoft.com>
References: <348_3599799A.7D76@edd.ca.gov>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7028
Article PK: 243058

I'm a bit confused about what you are trying to tell me. Do you get an
actual error message when trying to run the SELECT statement or the UPDATE
statement in the DBAdmin Painter? Or are you just asking about the sum
distinct?

--
Terry Dykstra [TeamPS]
Canadian Forest Oil Ltd.

progers2@edd.ca.gov wrote in message <3599799A.7D76@edd.ca.gov>...
>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?