Using dense_rank function in Peoplesoft Query Manager 2

[kernal]

I am using Peoplesoft's query manager (web-based) and don't know if the following is even possible.

PROBLEM:

The records in one table.

Term Test
1074 1-1
1068 1-1
1056 1-1

1074 2-1
1056 2-1

1074 3-1

RESULTS NEEDED:

Have 2 term fields retrieved. 1074 for 1 term field and just the prior term that the test was offered for the 2nd term field so it would look like:

Term Test Prior Term

1074 1-1 1068

1074 2-1 1056

1074 3-1 blank since this is the first term that the test was offered.

The following oracle select statement was given to me to get the results and I don't know if it is possible using the query manager (i.e. how to get dense_rank... in the query):

select term, test, ld "PRIOR TERM"
from (
select t.*,
dense_rank( ) over( partition by test order by term desc ) rnk,
lead( term ) over( partition by test order by term desc ) ld
from t
)
where rnk = 1
order by test, term desc

If this isn't possible then does anyone have any idea how to get the results I need using the query manager.

Help is appreciated. Thanks

 

[notadba]

Hi Kernal,

Not sure that this is going to be possible - as you need to do this kind of functions through the use of expressions. A few years back, PeopleSoft stopped you being able to use 'from' in any expression text as it allowed users to bypass the tables available in query trees...

You may be able to achieve a similar result using other sql work-arounds - but will depend on the database you are on.

Good luck - but I don't think that you will be able to do this with rnk functions.

 

[kernal]

Thanks notadba for the information about why the from in the expression is no longer allowed. It sure was nice when it was allowed. I'll try to figure out a different way.

 

[Rofeu]

Hello,

I assume that you've already figured out a way of doing it, but I've also had need for queries like this.

The way I go about it with PS Query is to use the same table thrice. Two of them link to eachother and one in a subquery:

SELECT T.TERM, T.TEST, (CASE WHEN T.TERM = T1.TERM THEN '' ELSE T1.TERM END)FROM T, T1 WHERE T.TEST = T1.TEST AND T1.TERM = (SELECT (CASE WHEN COUNT(T2.TERM) = 0 THEN T.TERM ELSE MAX(T2.TERM) END)FROM T2 WHERE T2.TEST = T.TEST AND T2.TERM < T.TERM) ORDER BY T.TERM, T.TEST DESC

You don't need a FROM in your expression this way.

Cheers,

Roel

 

[kernal]

Thanks Rofeu. I'll post another message as soon as I'm able to try your formula.

 

[kernal]

Your query worked great. Thanks alot Rofeu