Archive for the ‘Algorithms’ Category.

Reporting Changes In Peoplesoft With SQR, Part 1

Most of my report and interface programs output snapshots of the database. Reports and interfaces about how the data has changed are more challenging. The effective date model of many Peoplesoft tables suggest that we can easily extract the history of data, but the difference between effective dates and action dates, and the availability of correction mode complicate the issue.
Continue reading ‘Reporting Changes In Peoplesoft With SQR, Part 1’ »

Table Driven SQR

Table-Driven programming is a technique that can make a program more easy to write or less easy to write, more clear or less clear, more maintainable or less maintainable.  It can improve a program when we use it appropriately.
Continue reading ‘Table Driven SQR’ »

SQL Copy Function In SQR, Part 2

I wish SQL had a simple copy command. It would insert a new row into a table with almost all the values of an existing row of that table. Last week, we saw how to obtain data dictionary information from an Oracle database. This week our program will help us specify our needs and write SQL for us.

Continue reading ‘SQL Copy Function In SQR, Part 2’ »

SQL Copy Function In SQR, Part 1

I wish SQL had a simple copy command.  It would insert a new row into a table with almost all the values of an existing row of that table.
Continue reading ‘SQL Copy Function In SQR, Part 1’ »

Hooray For Arrays.Part(2)

Of course we can use arrays like tables.  What else can we do? Continue reading ‘Hooray For Arrays.Part(2)’ »

Hooray For Arrays.Part(1)

I’ve mentioned arrays while writing about other topics.  It’s time I write about arrays.  Someone could program in SQR for a whole career without ever using them, but that someone would have to work harder to do without these handy data objects. Continue reading ‘Hooray For Arrays.Part(1)’ »

Never Fear A Database Refresh Again

It is refreshing to have a new copy of the production database instance in the development database instance. It is also a bit frightening. Was anything important lost in the overwrite? Continue reading ‘Never Fear A Database Refresh Again’ »

Bad SQL Redesigned

The Assignment

Last week we looked at various techniques for combining tables.  As an example, we took an assignment “to gather the hours reported on timecards for each employee over a two week pay period.”  We saw five ways to combine the Peoplesoft HCM tables PS_EMPLOYEES and PS_TL_RPTD_TIME.

By the end, I realized that there was no actual business problem that could be solved correctly and efficiently with this approach. Continue reading ‘Bad SQL Redesigned’ »

6 Lessons Learned From Bad SQL

Here are some interesting variations of SQL syntax. The simple example I chose is probably ill suited for any actual program, but that makes it more interesting. Continue reading ‘6 Lessons Learned From Bad SQL’ »

Analyze Peoplesoft Process Instances With SQR

We built and enhanced a real-time dashboard that alerted us to problems with Peoplesoft process instances. The next challenge is to investigate processes that fail, or processes that succeeded yet dissatisfied our co-workers (the output was not as expected). Let’s build an SQR toolkit to help us do that. Continue reading ‘Analyze Peoplesoft Process Instances With SQR’ »