Tetris SQL

This is a DB2 solution. Oracle will be different for the 'daterange' WITH clause (subquery factoring)

-- Two step process:
-- Delete rows in Target that don't exist in Results, 
-- Insert rows from Result that don't exist in Target.

-- Tricky bit: 
-- Don't overwrite any manually added rows in the Target.
-- Instead block *around* them; Tetris.


-- DELETE
delete from TARGET_TBL d
where d.source = 'ee'
and 
    (processing_range.start_dt between d.start_dt and d.end_dt
    or processing_range.end_dt between d.start_dt and d.end_dt
    or d.start_dt between processing_range.start_dt and processing_range.end_dt)
and not exists
    (select 1
    from RESULTS_TBL a
    where a.pin_num = d.pin_num
    and a.start_dt = d.start_Dt
    and a.end_dt = d.end_dt)



-- INSERT
with daterange(level,dt) as (
    select 1, current date - 1 year
    from sysibm.sysdummy1
    union all select level + 1, dt + 1 day
    from daterange
    where level < 1000 and dt < current date + 1 year
)

select '1. Result' as thing, pin_num, start_dt, end_dt, 'ee' as source from RESULTS_TBL
union
select '2. Existing' as thing, pin_num, start_dt, end_dt, source from TARGET_TBL

union
select 
  '3. Answer' as thing
, a.pin_num

-- For PI/EA
, greatest(a.start_dt, 
    (select coalesce(max(s.end_dt) + 1 day, a.start_dt)
    from TARGET_TBL s
    where s.pin_num = a.pin_num
    and s.end_dt between a.start_dt and d.dt)) as start_dt

-- For PI/EA
, least(a.end_dt, 
    (select coalesce(min(s.start_dt) - 1 day, a.end_dt)
    from TARGET_TBL s
    where s.pin_num = a.pin_num
    and s.start_dt between d.dt and s.end_dt)) as end_dt

, 'ee' as source
from 
  RESULTS_TBL a
, daterange d
where d.dt between a.start_dt and a.end_dt
and not exists
    (select 1
    from TARGET_TBL x
    where x.pin_num = a.pin_num
    and d.dt between x.start_dt and x.end_dt)

Application Engine Log File

Handy little library to open Application Engine log files, based on whether running on client while developing, or on server.

class FuncLib
   method isTestingMode(&iProcessInst As integer) Returns boolean;
   method GetFile(&bTesting As boolean, &sFilename As string, &sMode As string) Returns File;
end-class;


/**********************************************************************
 * Returns whether app engine is in test mode (running locally).      *
 * -------------------------------------------------------------------*
 * Parameters  : &numProcessInst (process instance)               *
 * Returns   : True if running locally, otherwise false.              *
 **********************************************************************/
method isTestingMode
   /+ &iProcessInst as Integer +/
   /+ Returns Boolean +/
   Local boolean &bExists, &bTestMode;
   
   /* Check if process instance exists in a process scheduler table */
   SQLExec("SELECT 1 FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", &iProcessInst, &bExists);
   
   If Not &bExists Then
      /* If not, then its running in test mode. */
      &bTestMode = True;
   End-If;
   
   Return &bTestMode;
end-method;


/**********************************************************************
 * This method creates/opens a log file when running an app engine    *
 * in either 2 (testing) or n tier mode.                              *
 * -------------------------------------------------------------------*
 * Parameters                                                         *
 *   Parm 1:  Boolean - Testing mode?                                 *
 *   Parm 2:  File name                                               *
 *   Parm 3:  Mode: "W" will create a new file for writing,           *
 *                       "A" will append to an existing file.         *
 * Returns : File                                                     *
 **********************************************************************/
method GetFile
   /+ &bTesting as Boolean, +/
   /+ &sFilename as String, +/
   /+ &sMode as String +/
   /+ Returns File +/
   
   Local string &sPath;
   Local File &fFile;
   
   If &bTesting Then
      &sPath = "C:\TEMP\";
      &fFile = GetFile(&sPath | &sFilename, &sMode, %FilePath_Absolute);
   Else
      &sPath = "";
      &fFile = GetFile(&sFilename, &sMode);
   End-If;
   
   If &sMode = "W" Then
      MessageBox(0, "", 0, 0, "Creating file:  %1", &sPath | &sFilename);
   End-If;
   
   Return &fFile
end-method;

And to use:

import YOUR_PACKAGE:Utilities:FuncLib:*;

Component File &logFile;

&cbTestingMode = (create YOUR_PACKAGE:Utilities:FuncLib()).isTestingMode(YOUR_STATE_RECORD_AET.PROCESS_INSTANCE);
&logFile = (create YOUR_PACKAGE:Utilities:FuncLib()).GetFile(&cbTestingMode, "log.txt", "W");

PeopleCode to get a Sibling RowSet

For some reason this just took me far too long to figure out. Must be getting old. Writing down for reference.

Let's say you have a page with a Record structure like this:

Parent
   Sibling 1
   Sibling 2

And you want some code/event on Sibling 1 to change the data in Sibling 2.

The somewhat unusual scenario I had is that Sibling 2 is a kind of audit record for Sibling 1, so that every time data changed on Sibling 1 a new row was inserted into Sibling 2 with Oprid and DateTime etc.

Normally you could just have Sibling 2 as a child of Sibling 1 (and technically it is) but in my scenario Sibling 1 was already at Level 3. The data  was to be displayed. I had no room to move.


Place the following in something like SIBLING_1.Field1.SaveEdit()

Local Rowset &rsSIBLING_2 = GetRowset().ParentRow.GetRowset(Scroll.SIBLING_2);

You'll now have the sibling Record as a RowSet.

.ParentRow is the property of the RowSet you are looking for.

Make a Field Required using PeopleCode

Simple example of controlling whether a Field is required using PeopleCode instead of the Record definition properties.

Place this in the Record Field's SaveEdit PeopleCode.

If None(GetField().Value) Then
   GetField().SetCursorPos(%Page);
   GetField().Style = "PSERROR";
   Error MsgGet(15, 30, "Highlighted fields are required.");
Else
   GetField().Style = "PSEDITBOX";
End-If;

Randomly Generated Data - Oracle SQL

select
  trunc(dbms_random.value(1, 11)) as seconds
from dual
connect by level < 11

(You could join the results to a table using rownum I guess)

SQL to Group or Chunk Date Time Fields by n Minutes

Say you want to chunk some statistics data into 10 minute intervals. as of Oracle 11g you can use the extract() function that will pull out hours, minutes and the like from a DateTime column.

select
  to_date(trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0'), 'dd-mon-yy hh24:mi') as dttm_stamp
, count(some_field) "count"
from 
  ps_statistics_table
group by
  trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0')
;

Note the +0.1 bit that handles the range of results, up to that point spanning from 0 to 6. We can't have nn:60 as a vaild DateTime. This is also why we have the -10 part.

Also note that depending on the platform that is actually executing the SQL you may have to fiddle around with the DateTime formats. 'dd-mon-yy hh24:mi' might not cut it for you. PeopleSoft will require 'yyyy-mm-dd hh24:mi' for example.

A breakdown of the entire line:

select
  dttm_stamp as a
, extract(minute from dttm_stamp) as b
, ceil(extract(minute from dttm_stamp)/10 + 0.1)  as c
, ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10 as d
, lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0') as e
, trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0') as dttm_stamp
from ps_statistics_table
;

The final results from the first query:

17/11/2013 10:00:00 AM 99
17/11/2013 10:10:00 AM 120
17/11/2013 10:20:00 AM 50
17/11/2013 10:30:00 AM 10
17/11/2013 10:40:00 AM 65
17/11/2013 10:50:00 AM 77
17/11/2013 11:00:00 AM 189
17/11/2013 11:10:00 AM 201
17/11/2013 11:20:00 AM 235
17/11/2013 11:30:00 AM 188
17/11/2013 11:40:00 AM 105
17/11/2013 11:50:00 AM 77

Merging PDFs Produces Blank Pages

So you're creating a number of individual PDFs, doing something with them, and then you want to merge them all into one, nice, long PDF at the end.

Something like this:

/* Merge! */
Local boolean &booMergeSuccess = &objMergeTool.mergePDFs(&arrFiles, &sNSMergedFileDestination | &sDirSep | "merged.pdf", &sError);

You can see this in Communication Generation for example.

But when you open the PDF only the first page has data, the rest are blank. What's going on?

I finally reduced this down to being something "wrong" with the individual PDFs that were being created, before they were merged. It wasn't the templates. It was something specific to the PDF files themselves.

Comparing them to an environment where (luckily) the merge did work showed that the PDFs that did merge had no security, while the ones that didn't merge did have security. File > Properties > Security.

xdo.cfg was the same in both environments. So where was this coming from?

I noticed that, in 8.53 at least, there are some new parameters for BI Publisher Report Definitions, namely PDF Security. The default for pdf-security was True.

I changed this to False and the merge worked. Finally.

This is overriding the global value at the report level. The global value is set at:
Reporting Tools > BI Publisher > Setup > Global Properties

tl;dr solution:

update PSXPGLBPROP
set propvalue = 'False'
where proplname = 'pdf-security';