Archive for the ‘SQL’ Category.

SQL for Breaks in a Sequence

In a previous post, Views You Can Use: Ordinal Numbers, we discussed how to acknowledge children who visited Pee Wee Bees most often.  The little boy who had been here the most (88 times to date) should be in first place.  The little girl who has visited 83 times should be in second place.  It’s easy to count each child’s number of visits; harder to assign ordinal numbers (first, second, third) and recognize ties (two siblings have been here 79 times).

I also have a “Hot Streaks” list that shows the children who have visited Pee Wee Bees at least once per calendar month for at least the past three calendar months.  This was a more challenging query.  A child who visits once in January, once in February, and once in each following month throughout 2012, would have a streak of twelve consecutive months.  A child who visited ten times in September, ten times in October, not once in November, and ten times in December would have a “streak” of one month.

Continue reading ‘SQL for Breaks in a Sequence’ »

Views You Can Use: Ordinal Numbers

Cardinals and Ordinals

As a child, I learned about cardinal and ordinal numbers. I just checked Wikipedia and found that my understanding of them is … childlike. I remember cardinal numbers as non-negative integers; 0, 1, 2, 3, and so on. I remember ordinal numbers as first, second, third, and so on.

The difference is as follows. Suppose we have ten numbers listed from smallest to largest; 2, 3, 5, 7, 11, 13, 17, 19, 23, 29. (Can you guess how I chose them?) The first number is 2. The second number is 3. The ninth number is 23.

Continue reading ‘Views You Can Use: Ordinal Numbers’ »

From Process To Peoplesoft Page

Last week we discussed finding the best Peoplesoft page, and the menu path to that page, for viewing and updating a particular database table.  We have a similar need to find the menu path to the run control page for a particular batch process.
Continue reading ‘From Process To Peoplesoft Page’ »

From Database Table To Peoplesoft Page

When I discuss data with my colleagues in the Human Resources and Payroll departments, we speak different languages.  I talk about tables and columns.  They talk about menus, tabs, and pages.  “You need to change EEO4CODE in PS_JOBCODE_TBL,”  I’ll say.  They’ll reply, “You mean, select Setup HRMS, Foundation Tables, Job Attributes, Job Code Table, and the Job Code Profile tab?”  There are pages I’ve never visited and they’ve never used a SQL browser.
Continue reading ‘From Database Table To Peoplesoft Page’ »


PS_EMPLOYMENT used to be a SQL table.  Now it is a SQL view that joins PS_PER_ORG_ASGN, PS_PER_ORG_INST, and PS_JOB.  Of course, PS_JOB has two subselects to pick a single value of EFFDT and EFFSEQ for each value of EMPL_RCD.  The subselect for EFFDT is unusual and it may not behave the way the designers intended.

Continue reading ‘PS_EMPLOYMENT Bug Report’ »

Avoid Subqueries

I have nothing against subqueries, but there are times in the Peoplesoft environment that they are unnecessary.  If we can get exactly the same results with simpler, faster SQL, let’s avoid subqueries.
Continue reading ‘Avoid Subqueries’ »

Views You Can Use: Position Data

Some organizations manage their workforces with positions.  A position specifies a job and a place on an organization chart, which can exist without a person or with more than one person.  Position management can give organizations more control over their structures, budgeting, and succession planning.
Continue reading ‘Views You Can Use: Position Data’ »

Views You Can Use: Leave Accrual

Peoplesoft HCM has tens of thousands of record definitions, representing tables, views, and other Peopletools objects.  Nonetheless, we can find more ways to organize our data usefully for easy access in Query Manager or in database browsers like SQLPlus and TOAD.  This is the first of an occasional series of posts regarding new ways to access our data.  This week: PS_LEAVE_ACCRUAL.
Continue reading ‘Views You Can Use: Leave Accrual’ »

Little Known SQL Syntax

We may be designers and programmers, but we often need to look at the contents of the database to help our customers deal with problems.  I probably spend as much time writing SQL select statements in Quest Software’s TOAD (Tools for Oracle Application Developers) as I do writing SQR in SlickEdit.  I rarely go beyond the twelve basic keywords (select, from, where, and, or, not, like, exists, order by, group by, in), but there are other syntactical choices.  I’m not necessarily recommending them; they may be slower than the more familiar choices.

Continue reading ‘Little Known SQL Syntax’ »

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’ »