Developer Developing.

A newbie peoplesoft developer exploring development.

Using XMLagg

So, I’d been working with pivot queries. This is because there’s want for a report containing all the campuses a subject is taught at by subject code, which also contains any related subjects, and the campuses which the related subjects are being held. For each subject code they want all the relevant information to appear on a single row. This information spans a number of tables, including the campus table which brings back all of the campuses as separate rows. One way of doing this is with the pivot query. With this we’d turn those rows into columns, giving each campus its own column. Doing this gave the report an extra 14 columns, and in _most_ cases only one of these were used. It produced an ugly report with many empty spaces in most cases, it would be my guess that this isn’t what is desired. (I mean, they’re formatting it into this report format for a reason, probably related to readability.) So, when trying to come up with solutions, the fellow who introduced the functional team member to pivots showed me this little bit of code:

, rtrim (xmlagg (xmlelement (a, f.campus || ‘,’)).extract (‘//text()’), ‘,’)

He used it in place of selecting f.campus. I did a little googling to see if I could understand this piece of magic code. So, from what I can gather xmlagg is an aggregate function which returns a set of aggregated xml elements. The xmlelement command is turning the stuff following it in brackets into xml elements, with some sort of name. The a in this case is the name and the f.campus is the stuff what is being turned into an xml element. The || ‘,’ looks like “Append a comma after each element” to me. The next part “.extract (‘//text()’), ‘,'” is still pretty magic to me though. (We’re extracting text? Something about those single commas?)

Still having an issue. This works perfectly for the related programs, but not so well for the campuses of the main programs. In the case of having a related program with multiple campuses, then the main campuses display too many times. If there are three related campuses and one main campus, that main campus will display thrice. Which is not ideal. Solving this issue is probably related to the way the whole selects are done, rather then the xmlagg function.

Advertisements

Using pivot queries in peoplesoft.

I neglected this space almost as soon as I made it, but I’ve hit a wall in what I’m doing and perhaps some writing would get me on a productive bent again.

A different fellow in the office introduced one of the functional team members to pivot queries.

I’m only starting to get a handle on pivot queries myself, and have not yet written one which does exactly what I want it to do, so, this won’t be a tutorial on pivot queries generally.  (This seems like a pretty good explanation of them if that is what you’re looking for.)

The functional team member found out he was unable to implement this pivot query in the peoplesoft query building tool that he was working with, and I was given the task to do the same sort of thing without using the pivot. After a good deal of struggling with the SQL (I’m not all that experienced with it) my boss suggested to just put the pivot query inside a view and see if that could be queried effectively. It could. Seeing as a number of developers and the functional team member didn’t know this, it seems like a valuable thing to put out there: If you need a pivot query to work with the peoplesoft query builder, just put it in a view.

Processing through multiple rows and scrolls/levels for a page using rowsets.

For now here is some code I was having an issue with for processing through multiple scrolls on a page, with multiple rows in each scroll, to correctly produce error messages and set the cursor position to the field with the error.  I’ve used generic record names and page names for the example. At a later date I will provide an edit detailing the issues I was having and explaining how this code works.

Local Rowset &level0, &level1, &level2;

&level0 = GetLevel0();
&level1 = &level0(1).GetRowset(Scroll.FIRST_SCROLL);
For &I = 1 To &level1.ActiveRowCount
&var1 = &level1(&I).FIRST_RECORD.FIELD1.Value;
&var2 = &level1(&I).FIRST_RECORD.FIELD2.Value;
&level2 = &level1(&I).GetRowset(Scroll.SECOND_SCROLL);
For &J = 1 To &level2.ActiveRowCount
&var3 = &level2(&J).SECOND_RECORD.FIELD3.Value;
If (All(&Var1) Or
All(&Var2)) And
None(&Var3) Then
SetCursorPos(Page.PAGE_NAME, Record. FIRST_RECORD, &I, SECOND_RECORD.FIELD3, &J);
Error MsgGet(XXXXX, XX, “Message not found.”);
End-If;
If None(&Var1, &Var2) And
All(&Var3) Then
SetCursorPos(Page. PAGE_NAME, FIRST_RECORD. FIELD1, &I);
Error MsgGet(XXXXX, XX, “Message not found.”);
End-If;
End-For;
End-For;

Read more of this post

Hello World.

Hello, this first post will serve as an introduction.

I have been employed as a trainee peoplesoft developer for the past seven months. I am completing my IT degree part time, and I’ve not had any peoplesoft training which isn’t “on the job.” I’ve been doing a lot of learning in the past few months. This is my first development job and before I started working here I was entirely unfamiliar with peoplesoft and SQRs. My logic skills and general programming skills are currently at a beginners level. I have a lot of learning to be doing! This blog will primarily serve as a platform for me to explore problems that I am trying to solve, or to document issues I’ve had in the past.

I will be providing examples of code which I’ve used to solve problems, with an explanation of the code. This will be a learning aid for myself, but potentially helpful to other people struggling with similar issues.

The blog title “Developer Developing” primarily refers to my personal development as an applications developer,  my learning and general growth.