Tech Talk

LIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

Archives

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:

   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:

   0);

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.