This should work.  Ill give it a try
<Adam Brett> wrote in message
news:29BD5E74-8344-44E1-965A-BEED544B3AAC@news.elevatesoft.com...
> Ben
>
> I am also not 100% sure of your desired outcome, but here is a suggestion:
>
> CREATE VIEW ClearedTransactions as
> SELECT
> IDFieldName,
> SUM(Charges - Payments) as NetValue
>
> FROM Ledger
> GROUP BY IDFieldName
> HAVING NetValue = 0
>
> This creates an accessible dataset on the database which lists all the
> "fully paid" Transactions.
>
> You can now write a fairly simple UPDATE statement, using the
> ClearedTransactions VIEW in your WHERE clause:
>
> UPDATE Ledger
> SET(Keyoff = 'Value you choose')
>
> WHERE IDFieldName IN
> (SELECT IDFieldName FROM ClearedTransactions)
>
> To stop the above statement running on the whole database you might want
> to add an AND clause to the UPDATE statement so you only process
> transactions above a certain key-value ... or something.
>

Ben

>All these examples are where the condition is met by a single record. In my
>case the condition is contingent on the sum of at least tow records and
>perhaps more.

Yes but those are the examples I found. What you need to do is replace the subselect with your own specialise one. I can't help more than that until I'm given the sort of information I asked for. All I can do is make the best guess with no way of knowing what it really is you want.

Roy Lambert [Team Elevate]

I followed the above.  Here is the modified syntax:

To create the view correctly I used the following:

CREATE VIEW ClearedAPTrans as
SELECT
VenPhone, VenSeq, VenName, InvDate, InvNum,
TotalInv, TotalDM, TotalPaid,
(TotalInv - TotalPaid - TotalDM - TotalDisc) as InvBal
FROM (SELECT
          MIN(InvDate) as InvDate,
          (CURRENT_DATE() - InvDate) as ARAge, InvNum, RecSeq, VenPhone,
          VenSeq, VenName, PONum, SONum, KeyOff,
          SUM(COALESCE(AmtInv,0)) AS TotalInv,
          SUM(COALESCE(AmtDM,0)) AS TotalDM,
          SUM(COALESCE(AmtDisc,0)) AS TotalDisc,
          SUM(COALESCE(AmtPay,0)) AS TotalPaid
           FROM APLines
           GROUP BY VenPhone, VenSeq, InvNum) InvoiceTotals
          where (TotalInv - TotalPaid - TotalDM) = 0
  Order by VenPhone, VenSeq, InvNum

This will create the view just fine.

Now for the update.  I used the following syntax:

UPDATE APLines
SET Keyoff = (InvNum)
WHERE "VenPhone" and "VenSeq" and "InvNum" IN
(SELECT VenPhone, VenSeq, InvNum FROM ClearedTransactions)

I get the follwing error:

ElevateDB Error #700 An error was found in the statement at line 3 and
column 7 (Expected Boolean expression but instead found "VenPhone")

What is to be done.

Thanks
Ben

"Ben" <arrow1432@verizon.net> wrote in message
news:F930CBC6-308D-4BBA-87B4-3B07C80BDF25@news.elevatesoft.com...
> This should work.  Ill give it a try
> <Adam Brett> wrote in message
> news:29BD5E74-8344-44E1-965A-BEED544B3AAC@news.elevatesoft.com...
>> Ben
>>
>> I am also not 100% sure of your desired outcome, but here is a
>> suggestion:
>>
>> CREATE VIEW ClearedTransactions as
>> SELECT
>> IDFieldName,
>> SUM(Charges - Payments) as NetValue
>>
>> FROM Ledger
>> GROUP BY IDFieldName
>> HAVING NetValue = 0
>>
>> This creates an accessible dataset on the database which lists all the
>> "fully paid" Transactions.
>>
>> You can now write a fairly simple UPDATE statement, using the
>> ClearedTransactions VIEW in your WHERE clause:
>>
>> UPDATE Ledger
>> SET(Keyoff = 'Value you choose')
>>
>> WHERE IDFieldName IN
>> (SELECT IDFieldName FROM ClearedTransactions)
>>
>> To stop the above statement running on the whole database you might want
>> to add an AND clause to the UPDATE statement so you only process
>> transactions above a certain key-value ... or something.
>>
>
>

Ben,

<< I get the follwing error: >>

Use this instead:

UPDATE APLines
SET Keyoff = (InvNum)
WHERE ("VenPhone","VenSeq","InvNum") IN
(SELECT VenPhone, VenSeq, InvNum FROM ClearedTransactions)

Tim Young
Elevate Software
www.elevatesoft.com

Im trying this in the EDB Manager and I get the following error

(ElevateDB Error #401 The table or view ClearedTransactions does not exist
in the schema Default)
How do I make it exist in the EDBManagere

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:C3BA609D-7D0F-40D2-8BBA-FC66F94EE035@news.elevatesoft.com...
> Ben,
>
> << I get the follwing error: >>
>
> Use this instead:
>
> UPDATE APLines
> SET Keyoff = (InvNum)
> WHERE ("VenPhone","VenSeq","InvNum") IN
> (SELECT VenPhone, VenSeq, InvNum FROM ClearedTransactions)
>
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Thanks all for your help.  Its really appreciated.  Here is the final
version of the code that works:

CREATE VIEW "ClearedAPTrans" AS
SELECT
VenPhone, VenSeq, VenName, InvDate, InvNum,
TotalInv, TotalDM, TotalPaid,
(TotalInv - TotalPaid - TotalDM - TotalDisc) as InvBal
FROM (SELECT
          MIN(InvDate) as InvDate,
          (CURRENT_DATE() - InvDate) as ARAge, InvNum, RecSeq, VenPhone,
          VenSeq, VenName, PONum, SONum, KeyOff,
          SUM(COALESCE(AmtInv,0)) AS TotalInv,
          SUM(COALESCE(AmtDM,0)) AS TotalDM,
          SUM(COALESCE(AmtDisc,0)) AS TotalDisc,
          SUM(COALESCE(AmtPay,0)) AS TotalPaid
           FROM APLines
           GROUP BY VenPhone, VenSeq, InvNum) InvoiceTotals
          where (TotalInv - TotalPaid - TotalDM) = 0
  Order by VenPhone, VenSeq, InvNum

This creates an accessible dataset on the database which lists all the
"fully paid" Transactions.

You can now write a fairly simple UPDATE statement, using the
ClearedTransactions VIEW in your WHERE clause:

UPDATE APLines
SET Keyoff = (InvNum)
WHERE ("VenPhone","VenSeq","InvNum") IN
(SELECT VenPhone, VenSeq, InvNum FROM ClearedAPTrans)

Ben
"Ben" <arrow1432@verizon.net> wrote in message
news:DCF3D49C-ABBA-4FC8-914D-88A97512ABD4@news.elevatesoft.com...
> Im trying this in the EDB Manager and I get the following error
>
> (ElevateDB Error #401 The table or view ClearedTransactions does not exist
> in the schema Default)
> How do I make it exist in the EDBManagere
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:C3BA609D-7D0F-40D2-8BBA-FC66F94EE035@news.elevatesoft.com...
>> Ben,
>>
>> << I get the follwing error: >>
>>
>> Use this instead:
>>
>> UPDATE APLines
>> SET Keyoff = (InvNum)
>> WHERE ("VenPhone","VenSeq","InvNum") IN
>> (SELECT VenPhone, VenSeq, InvNum FROM ClearedTransactions)
>>
>> Tim Young
>> Elevate Software
>> www.elevatesoft.com
>>
>
>