Question and answer lost on a SQL query (answer from avegaart)

Question and answer lost on a SQL query (answer from avegaart)

Post by Franco Jomm » Sat, 27 Oct 2001 06:34:20



I have lost a few messages, so I will try to get again an answer to the
following subject that perhaps has already been given. Sorry for this. I
repoeat my question:

I am trying to do the following:
 1- From a MOVEMENTS.DBF file with details of financial operations, get the
 total of the amounts of each "account".
 2- Match the Account totals calculated against the BALANCES.DBF
(containing  Account and Balance), to get possible differences  in the
amounts.

I have saved the following answer from "avegaart":
Try:

 update "BALANCES.DBF" B inner join "MOVEMENTS.DBF" M
 on (B.account = M.account) and (B.year = M.year)
 set B.balance = sum(M.amount)
 where B.balance <> sum(M.amount)

I have tried, but I get "inner, invalid use of keyword".
I guess the update statement doesn't accept joined DBFs ?
To check for possible syntax problems I tried to leave out "inner join" but
got the same problem !
Any other suggestion ?

 
 
 

Question and answer lost on a SQL query (answer from avegaart)

Post by M.H. Avegaar » Sat, 27 Oct 2001 16:14:13


1. You can read back old messages on groups.google.com.
2. Does your database accept the 'inner' keyword at all ?
3. Have you tried:

update "BALANCES.DBF" B, "MOVEMENTS.DBF" M
set B.balance = sum(M.amount)
where (B.account = M.account)
and (B.year = M.year)
and (B.balance <> sum(M.amount))


Quote:> I have lost a few messages, so I will try to get again an answer to the
> following subject that perhaps has already been given. Sorry for this. I
> repoeat my question:

> I am trying to do the following:
>  1- From a MOVEMENTS.DBF file with details of financial operations, get
the
>  total of the amounts of each "account".
>  2- Match the Account totals calculated against the BALANCES.DBF
> (containing  Account and Balance), to get possible differences  in the
> amounts.

> I have saved the following answer from "avegaart":
> Try:

>  update "BALANCES.DBF" B inner join "MOVEMENTS.DBF" M
>  on (B.account = M.account) and (B.year = M.year)
>  set B.balance = sum(M.amount)
>  where B.balance <> sum(M.amount)

> I have tried, but I get "inner, invalid use of keyword".
> I guess the update statement doesn't accept joined DBFs ?
> To check for possible syntax problems I tried to leave out "inner join"
but
> got the same problem !
> Any other suggestion ?


 
 
 

1. Answers, answers, who has the answers?

First off, I'm still hoping to get some clues on how to set up a query to
perform the same series of actions on multiple fields in a table without having
to set up each field separately.

Now for a fresh set of questions...

1)  I've set up library code to open a multiple-selection form and get all the
selected values.  (ie, a list of contacts with a checkbox attached to each
displayed record, allowing the selection of all, some, or none of the people)
The values are tracked in a dynarray, but pdox refuses to let the library
method pass the dynarray back as a return value.  Can this be done by some
(relatively) direct means, or will the list of selections have to be passed
one-by-one?

1a)  I've noticed that Paradox only seems to track as many checkbox values as
the tableframe displays.  With DataArriveRecord, I've been able to set the
check to fit each record's actual select status as it receives focus, but not
as it comes onscreen (via moving through the list pagewise, for instance).
Any suggestions?

2)  Another library method is meant to jump from a master/detail form to a
form showing a the detail table in greater detail (say, from a Company
screen with a list of contacts to a Contact screen showing additional info
on a specific contact).  When coded specifically for each combination of
forms, it works, jumping to the desired detail record by attaching a tcursor
to a field, finding the contact, and resyncing the field.  The library version
needs to be told what field to attach itself to for this search since there are
no fields that are guarantted to be on every form; I passed this field ID to
the method as a UIObject-type variable named 'LinkField'.  All fine and well
until it hits the attach command and can't find a UIObject on the new form
named 'LinkField'.  I tried having it attach to LinkField.Value, but this
generated a syntax error (which was hardly a surprise).  How can this be
genericized?

3)  Can a tab order be established within a multirecord object without OPAL
coding?  Fields within the MROs I've tried don't respond to tab/backtab at
all...

4)  For the purposes of the DataNextSet/DataPriorSet actions, how is a 'set'
defined?  It appears to default to 10 records.  Can a different number of
records be specified?  For a table indexed on a string, can a 'set' be defined
as all records with the same first character in the index field?

5)  Say you've got a form showing two tables in a 1-1 relationship.  One table
is, of course, defined as the 'master' table.  How can a search be done on one
of the other table's fields?  What if the relationship is 1-many and the
master table is on the many side?  How about searching through a detail table?
(Do you get the impression that I'm less than satisfied with Paradox's default
DataSearch behavior?)

Th-th-th-that's all (for now) folks!


2. Control Center problems - connections

3. Lost Record in Answer Table with running a query including two tables in Paradox 5.0

4. SQL Agent

5. Old question but i lost the answer

6. custom solution needed

7. SQL Query Question -- Easily Answered

8. Database for Windows & Mac?

9. Can an answer table append an answer table?

10. a query question,does anybody know the answer.

11. pdx7/95 losing answer,db

12. SQL Query - EQ answers it!

13. SQL query answer needed