When Peoplebooks Is Not Enough
Archive for the ‘SQL’ Category.
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’ »
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’ »
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’ »
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.
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’ »
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’ »
Here are some small but significant ways to speed up or streamline your SQR program. Continue reading ‘7 Little SQL Tricks’ »