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.

Re: calculation

2 posts in PB Infomaker Report Writer Last posting was on 1999-06-02 01:39:11.0Z
Richard Posted on 1999-05-27 16:34:25.0Z
Newsgroups: sybase.public.infomaker.general
From: "Richard" <georgehills2@worldnet.att.net>
Subject: Re: calculation
Date: Thu, 27 May 1999 09:34:25 -0700
Lines: 56
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_5KRDe6Fq#GA.267@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6274
Article PK: 241258

Ok, I created the group, and it's calculating the correct data. BUT, I'm
getting duplicates in the detail section. In Detail, I have payment_id and
payment_amount, and it's bringing in two to four identical payments. I went
to Query Governor and selected Distinct, and I'm still having the duplicate
problem.

In SQL, I only have one join from the Payment table (payment.claimant_id =
claimant.claimant_id). I tried to add a second join, like payment.claim_id
= claim.claim_id, but I got an error: The WHERE clause could not be
interpreted successfully..."

Would another join eliminate the duplicates? I need to eliminate the
duplicates in the payment_id column, and I think it would be ok.

Terry Dykstra wrote in message <73V7P#sp#GA.273@forums.sybase.com>...
>You can create conditional sums:
>sum ( if (payment_transaction_desc = 'Adjuster Expense', amount, 0) for
>group x )
>
>--
>Terry Dykstra (TeamSybase)
>Canadian Forest Oil Ltd.
>Richard wrote in message ...
>>I'm fairly new with IM, so bare with me. We're using IM50 and our DB is
MS
>>SQL 6.5. We use our DB to track insurance claims. We have one report
that
>>summarizes claim records, provides financial totals, etc. Each total is,
>>obviously, based on several payment records, which do not show on the
>>report. We have two main categories of payments: Indemnity and Expense.
>>There are several subcategories under each.
>>
>>I want to total a particular subcategory of Expense, called "adjuster
>>expense." There is no speficic column called adjuster_expense_total, so I
>>need to use a calculation to add up a claim's adjuster expenses. The
>>problem as I see it is that this particular report is on the claimant
level
>>(by claimant), not the payment level. Can I create a calculation that
will
>>add up payments when the payment_transaction_desc = 'Adjuster Expense'??
>>
>>So...each record on this report is on the claimant_id level. "Adjuster
>>Expense" is coming from the payment_transaction_desc table. So in lehman
>>terms:
>>
>>I want to sum all amounts (payments tied to that claimant_id) when the
>>payment_transaction_desc = 'Adjuster Expense'. Also, some claims may not
>>have any Adjuster Expenses yet. Any ideas. Thanks.
>>
>>
>>
>
>


Terry Dykstra Posted on 1999-06-02 01:39:11.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra" <tdykstra@cfol.ab.ca>
Subject: Re: calculation
Date: Tue, 1 Jun 1999 19:39:11 -0600
Lines: 74
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_IcLpWlJr#GA.267@forums.sybase.com>
References: <348_5KRDe6Fq#GA.267@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6259
Article PK: 240194

Can you show the SQL. It makes it easier to troubleshoot.
In the SQL you can select the distinct option. (Design, Distinct). All
the Query Governor does is to (dis)allow this option.
Since your join is on claimant_id, this is normally too what you would use
to create a group on the report. Fields that should not be repeated should
go in the group.
On the detail band you can also use the 'Rows, Suppress repeating values'.
It doesn't remove duplicate rows, just doesn't print fields that have
identical values in the previous row.

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.

Richard wrote in message <5KRDe6Fq#GA.267@forums.sybase.com>...
>Ok, I created the group, and it's calculating the correct data. BUT, I'm
>getting duplicates in the detail section. In Detail, I have payment_id and
>payment_amount, and it's bringing in two to four identical payments. I
went
>to Query Governor and selected Distinct, and I'm still having the duplicate
>problem.
>
>In SQL, I only have one join from the Payment table (payment.claimant_id =
>claimant.claimant_id). I tried to add a second join, like payment.claim_id
>= claim.claim_id, but I got an error: The WHERE clause could not be
>interpreted successfully..."
>
>Would another join eliminate the duplicates? I need to eliminate the
>duplicates in the payment_id column, and I think it would be ok.
>
>
>Terry Dykstra wrote in message <73V7P#sp#GA.273@forums.sybase.com>...
>>You can create conditional sums:
>>sum ( if (payment_transaction_desc = 'Adjuster Expense', amount, 0) for
>>group x )
>>
>>--
>>Terry Dykstra (TeamSybase)
>>Canadian Forest Oil Ltd.
>>Richard wrote in message ...
>>>I'm fairly new with IM, so bare with me. We're using IM50 and our DB is
>MS
>>>SQL 6.5. We use our DB to track insurance claims. We have one report
>that
>>>summarizes claim records, provides financial totals, etc. Each total is,
>>>obviously, based on several payment records, which do not show on the
>>>report. We have two main categories of payments: Indemnity and Expense.
>>>There are several subcategories under each.
>>>
>>>I want to total a particular subcategory of Expense, called "adjuster
>>>expense." There is no speficic column called adjuster_expense_total, so
I
>>>need to use a calculation to add up a claim's adjuster expenses. The
>>>problem as I see it is that this particular report is on the claimant
>level
>>>(by claimant), not the payment level. Can I create a calculation that
>will
>>>add up payments when the payment_transaction_desc = 'Adjuster Expense'??
>>>
>>>So...each record on this report is on the claimant_id level. "Adjuster
>>>Expense" is coming from the payment_transaction_desc table. So in lehman
>>>terms:
>>>
>>>I want to sum all amounts (payments tied to that claimant_id) when the
>>>payment_transaction_desc = 'Adjuster Expense'. Also, some claims may not
>>>have any Adjuster Expenses yet. Any ideas. Thanks.
>>>
>>>
>>>
>>
>>
>
>