|Generating SQL||KEVIN RESCHENBERG|
We often have a need to create SQL for various database
updates or for inclusion within SQR or App Engine programs.
Normally we would write it using a text editor or
directly within the SQL tool (Query Analyzer, Toad or similar).
However, there are times when this may not be practical or
efficient. Here are a couple of alternatives: PeopleSoft
Query and Excel.
Many people use PeopleSoft Query to create SQL. This can be
a good way to get a starting point when your SQL contains
many joins. It includes row-level security (whether you want
it or not), so remove the security views and
joins if you do not need them. Also, be sure to review the
SQL. Depending on the order in which you added tables to your
query, it is possible to miss a join (on a SETID, for example).
Sometimes you can also simplify the generated SQL. For example,
Query might go to the set control table to get a SETID that
you may already have (from JOB).
Another way to create SQL is by using Excel. When a user gives
you a spreadsheet containing information for a database
update, you can often use that as a base for building your
SQL. Here's how.
Suppose that you need to insert hundreds of rows into table
PS_MY_TABLE, which contains columns EMPLID, NAME and
AMOUNT. All of the AMOUNT fields will be 0. You have a
spreadsheet containing the values of the other two fields:
We need to create SQL in this form:
INSERT INTO PS_MY_TABLE VALUES ('000001','Doe,John',0);
First, insert new column A into the spreadsheet and enter:
INSERT INTO PS_MY_TABLE VALUES (
and copy it all the way down to the last line.
In column D (located after the last of the original
two columns of the spreadsheet), enter:
and copy that all the way down. Save the spreadsheet in CSV (comma-delimited) format
by selecting this format in the "Save as type" field of the
Save As dialog.
Open the .CSV file in a text editor (not Word). Here is what we have:
INSERT INTO PS_MY_TABLE VALUES (,000001,"Doe,John",0);
INSERT INTO PS_MY_TABLE VALUES (,000002,"Smith,Mary",0);
That's close but not quite right. We need to remove the first comma
and change the double quotes to single quotes (if required by
your database). Use the text editor's find/replace feature
to change all occurrences of the following:
Change this To this
And that's it. Your SQL should be ready to run. One important
point: If you use this method, be sure to save a copy of the
original spreadsheet—just in case!
Next week I'll talk about using SQL to generate SQL.