Long readable SQL in peoplecode

Readable SQL statements

Problem 

First I'm going to admit when it comes to my code I'm a minimalist.  I like short clean code and I often use any shortcuts available in a language.  However, I will not sacrifice readability only to have the smallest line count.  Some languages you could get away with writing an entire function in 1 return statement but is a nightmare to read or modify.  For a while now I've been in pursuit of how to deal with long SQL statements in PeopleCode.  There are a few problems with this language that make writing long friendly SQL statements.  There is no way to continue a string on multiple lines without string concatenations.  There is not a shorthand append string that some languages have such as += either.

&sqlQry = " select * from PS_JOB A";
&sqlQry = &sqlQry | " where emplid = :1 and empl_rcd = :2 ";
&sqlQry = &sqlQry | " and effdt = (select max(effdt) from PS_JOB B";
&sqlQry = &sqlQry | " where A.emplid = B.em... ";

You can see where this is going. It's not to bad approach, however your indentation is off and your long query names waste space on that could be used for the query itself and actually reduce the lines you need. I finally settled with using &qry but I still wasn't satisfied with the doing the concatenation method on every line. What if I needed multiple queries and wanted a more descriptive name.

My solution

Here is my approach for now until they come up with += shortcut for string concatenation or a way to build a string on multiple lines.  I define a very simple several line short named variable scheme (&sq1, &sq2...) for each query line.  Then string the set together into the longer descriptive name and reuse them on my next query if needed.

Local string &sq1, &sq2, &sq3, &sq4, &sq5;
Local string &queryJobs, &queryEmpl;

/* query the jobs table */ 
&sq1 = " select * from PS_JOB A";
&sq2 = " where emplid = :1 and empl_rcd = :2 ";
&sq3 = " and effdt = (select max(effdt) from PS_JOB B ";
&sq4 = "            where A.emplid = B.em.....  ";
&queryJobs = &sq1 | &sq2 | &sq3 | &sq4;

/* query the Employee table */
&sq1 = " select * from PS_employees A";
&sq2 = " where emplid = :1 and empl_rcd = :2 ";
&queryEmpl = &sq1 | &sq2;

Just remember to put a space in at the beginning and/or ending of every line.

Using SQL Object

An alternative way is to use an SQL object. Just create a new SQL object from the menu File/New/SQL. Enter you valid SQL statement with the parameters you wish again using the same ":1" placeholders. Then call that SQL in your peoplecode with SQLEXEC and pass your parameters values in as shown below.

Local Record &rec = CreateRecord(Record.JOB);
SQLExec(SQL.MYSQL_JOB, &emplid, &rcd, &rec);

Expanding your Meta-SQL for validation

PeopleCode META-SQL getting the expanded statement

I couldn't find a way to get PeopleCode to log or dump the expanded SQL. This is necessary to confirm the META-SQL has expanded into the query you expected. I finally found a way to get your expanded SQL.  Here is how you do it.

1. Create a new SQL Definition
2. Paste your SQL syntax form your Application Engine PeopleCode
3. Replace all your variables with a dummy values or in the case of a record holder put the record name
4. Right click on the query screen and select "Resolve Meta Data"
5. Look down at the output Meta SQL screen.  Voila expanded full SQL.

SQL for peoplesoft date values

Current Date

I needed a way to reset a table where the effective date was today. I had a query that worked with TO_DATE('24-02-19','yy-MM-dd') except every day I'd have to update the query to match the current date. Sometimes I'd forget and delete some values I may have wanted. In oracle if you use any current date function like sysdate, current_date etc the current time is actually included. To day where effective date is equal to the current_date would never match unless you ran the query at exactly midnight to the millisecond of the day you wanted removed.

Proof

select to_char(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss') from dual;

To get around this I used the TRUNC function to zero everything after the the Date.

Example

select to_char(trunc(CURRENT_DATE,'DDD'), 'dd-mon-yyyy hh24:mi:ss')
from dual;
-- The delete statement would like like this.
delete from PS_MCM_SNLF_H_REC 
where EFFDT = trunc(CURRENT_DATE,'DDD');

Categories and search screen with Long Translate values only

I had a mapping table that each group of mapping belonged to a category.  The requirement was to make a search page for just the category displaying the long description.  For each category the user could add multiple mappings.  Here is how I created the custom search page for just a list of category translate values.

Field

Create my category field MCM_ADS_CAT which had about a dozen translate values.

Record

My page required a parent record that included my new category, effective date and effective status.  The child record obviously included category, effective date followed by my addition fields for the mappings.

Page

I created a page to edit the listings within each category or Translate grouping and effective date.  This page requires 2 scroll levels to work properly the first level being the parent record and the second level the grid is the child data.  The initial search record or ADS Category you see at the top is actually a View that I will describe later.

Component

The component includes the new page but for the search record we are going to use a new view that selects the distinct values from our parent record.

View

The view is where I need to get the distinct values along with the long description translate values.  This view will be used as the search for record on my new component.  The view includes 2 fields my MCM_ADS_CAT and the XLATLONGNAME.  Here is the SQL to get the distinct in use values with long description from the translate items table PSXLATITEM.

SELECT DISTINCT aa.mcm_ads_cat, bb.xlatlongname 
  FROM ps_mcm_ads_eff aa, psxlatitem bb 
 WHERE aa.mcm_ads_cat = bb.fieldvalue
   AND bb.fieldname = 'MCM_ADS_CAT'
   AND bb.effdt = ( SELECT MAX(cc.effdt) FROM psxlatitem cc 
                    WHERE bb.fieldname = cc.fieldname 
                      AND bb.fieldvalue = cc.fieldvalue 
                      AND cc.eff_status = 'A')

Now on your view record field set the following properties to use only the long translate value on the search page:

 

ADS_CATEGORY

• Key Yes
• Search Key Yes
• List Box Item No

XLATLONGNAME

• Key No
• Search Key No
• List Box Item Yes

Search Page Results

Chrome Peoplesoft Object details

If you are using Chrome browser to view the object details and you can't do the Ctrl+J trick.  In chrome it is the shortcut to open downloads.  To get it to work you need to press J first then Ctrl and release J.  This will trigger the peoplesoft trick instead of Chromes download page.

J + Ctrl + J

Peoplecode experimenting with pages

In need to hack up a page?

I'm often in need to experiment or completely hack up a page trying to figure out a different way to solve a specific problem.  I don't really want to delete and destroy a working copy of a page in case my idea doesn't work.  So I found this great way to build a addition pages and flip between them.

Steps

1. Build or use an existing working page already in a registered component.
2. Open your page and "Save As" a second copy with a new name.
3. Drag the page to your Component.
4. Using the Hidden checkbox you can hide your original page and use your new page.

Now you can hack up your new page as much as you like and even go back and view your original page just by flipping the Hidden value on your component.