FilePro2SQL Rosetta Stone

[Walter Vaughan]

FilePro2SQL Rosetta Stone

I'm going to propose to start a publicly available wiki for those of us with 
decades of filePro experience who also have to do the same things with SQL 
databases. It could serve two purposes, today and thirty years from now.


Here's a simple case...
=================================================================================

Say you wanted to update a field in a record with data from that same record. In 
the case of filepro you would most likely create a processing only report and a 
table to process.

In the following examples in an other field.

     * field_1 contains data like "XX45"
     * field_2 contains data like "U-ZZ"
     * field_3 is supposed to contain the numeric value we want to get from 
field_1 that are the third and fourth characters

filePro Code

     If: 3 eq "" and 2 co "U-"
   Then: 3=mid(1,"3","2")

SQL Code

update table_name
    set field_3 = int4(substring(table_name.field_1,3,2))
    where (table_name.field_2 like 'U-%' and field_3 is null)

     * Since field_3 is numeric we have to cast the output of the substring 
command with int4()
=============================================================================

Something like this could be hosted at http://www.howtoforge.com, but I am open 
to better places. I'd just like to be able to google
[filePro2SQL select subtotals] and create/view/update documented examples for 
more than just myself.

I'd prefer someplace that uses Mediawiki markup.

Ideas?