Transferring records from one table to another

[AndrewMozley]

I am transferring records (for a specific nominal account, selected by range of dates) from TableA into a cursor Cursor1 for reporting, and while doing this I gather further descriptive information from another table, either Customer or Supplier. I am doing this in a way that I am familiar with, but I wonder whether I ought to be using SQL more comprehensively. I do use SQL a little, but am not very familiar with it.

Here, in essence is my code; TableA is ordered by date within nominal account

*  Nom_account and Start_date have been established

SELECT TableA
SEEK Nom_Account
SCAN FOR Tran_date >= start_date WHILE TableA.account = Nom_account
   SELECT Cursor1
   APPEND BLANK
   REPLACE thisfield WITH TableA.Thisfield, Thatfield With TableA.ThatField . . .
   DO CASE
     CASE TableA.RecType = “C”
      SELECT Customer
      SEEK TableA.Related_Account
      lcName = Customer.account_name
     CASE TableA.RecType = “S”
      SELECT Supplier
      SEEK TableA.Related_Accont
      lcName = Supplier.account_name
      ENDCASE
   SELECT Cursor1
   REPLACE cName WITH lcName
   *  maybe replace some other fields in Cursor1
   ENDSCAN

I feel that I may be doing too much SELECTing in this code!. If anyone has a more elegant way of achieving the task, I would be grateful.

I have given the basic structure of the code - there are one or two complications - for example there is in fact a TableB, similar to table A, which is also scanned to gather information forCursor1, but the core of my concern is whether there is a neater way of gathering the account name information from Customer and Supplier.

 

[Mike Lewis]

Andrew,

Off the top of my head, you could do something like this:

SELECT aa.thisfield, aa.thatfield, ;
  ICASE( ;
      aa.RecType = "C", cust.AccountName, ;
      aa.RecType = "S", supp.AccountName) AS AccountName ;
  FROM TableA aa ;
  JOIN customer cust ON aa.Related_Account = cust.Customer ;
  JOIN supplier supp ON aa.Related_Account = supp.Supplier ;
  WHERE aa.Tran_date > start_date AND aa.Account = Nom_Account ;
  INTO CURSOR Cursor1 READWRITE

I don't guarantee that is completely correct, but it should give you a good start.

Note that I've used aa as an alias for TableA, rather than simply a. That's because I have an irrational prejudice against one-letter aliases. It's probably misplaced, but I'm too old to change now.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Overall, if you use VFP9 you have the syntax

[pre]INSERT INTO targettable (fieldlist) SELECT fields FROM Sourcetable[/pre]

The fieldlist in the first part is optional, if skipped, the following SELECT-SQL query needs to select a set fitting the whole target table fieldlist.
And the query you do with the SELECT part can combine data from several sources, eg it can be a compex query with joins.

Bye, Olaf.

 

[Mike Lewis]

I guess that an important difference between

[tt]SELECT ... INTO Cursor1[/tt]

and

[tt]INSERT INTO Cursor1 (fieldlist) <SELECT statement>[/tt]

is that the former will create the cursor if it doesn't already exist (and will overwrite it if it does), while the latter relies on the cursor already being in existence.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

OK, I didn't consider this. It depends a bit how Cursor1 is created previous to this code, eg it may be an empty copy of some other table, which is then filled. In some cases, that may be easier than generating it from a query, especially if there are some fields with default values populated by the default expressions. You can also use expressions in the firld list of a query, but it's easier to maintain default values/expressions in a dbf than in code.

Bye, Olaf.

 

[AndrewMozley]

Thank you both; I will experiment with this - may almost move into the 21st century by learning to use the ICASE statement.

Yes, I see that if I am adding from two tables, I would use SELECT INTO Cursor1 for the first extract and INSERT INTO Cursor1 for the second.

Andrew

 

[Mike Lewis]

if I am adding from two tables, I would use SELECT INTO Cursor1 for the first extract and INSERT INTO Cursor1 for the second.

That's true, but it slightly misses the point. With both solutions, you can do the job in a single statement. The choice of which statement to use depends mainly on whether the cursor already exists and you now want to add records to it, or whether you need to create the cursor each time you run the process.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike has explained that very well. There is no need to do two steps because data comes from two tables. Only, if you can't cope with it otherwise, eg the query is too complex or too hard to maintain. Joining data from two tables is the mildest complexity you can have besides single table queries.

Bye, Olaf.