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);