Go Premium for a chance to win a PS4. Enter to Win


Peoplesoft Query

Posted on 2008-10-15
Medium Priority
Last Modified: 2012-06-21
I have written a SQL which I want to translate into a peoplesoft query. However I have problems building this query in peoplesoft. The SQL I have is:

SELECT A.Name, PRV.Value1, A.Value2, PRV.Comment
FROM Table1 AS A, Table2 AS B, Table2 AS D
WHERE A.Name=D.Name
AND B.Value1= (SELECT MAX(C.Value1) FROM Table2 C where C.Name = B.Name)
AND D.Value1= (SELECT MAX(C.Value1) FROM Table2 C where C.Name = B.Name and C.Value1 <B.Value1);

The reason I couldn't build this in peoplesoft is because after I aggregated C.Value1 (max), it doesn't allow me to add a criteria to it. The criteria is C.Value1 < B.Value1.  So the problem part is:

'AND D.Value1= (SELECT MAX(C.Value1) FROM Table2 C where C.Name = B.Name and C.Value1 <B.Value1)'

Do I need to rewrite this SQL or is there a way to build this query in peoplesoft?
Question by:jannhan
  • 4
  • 3
LVL 74

Expert Comment

ID: 22728023
You've obfuscated your query a bit too much.  It's hard to follow what you intended.

you have prv.value and prv.comment but you don't have anything called "prv" in your from clause.

Is prv a package or is that a mistake in your posting?


Author Comment

ID: 22728051
Sorry I posted the wrong sql. The correct being:

SELECT A.Name, A.Value2, D.Comment, D.Value1
FROM Table1 AS A, Table2 AS B, Table2 AS D
WHERE A.Name=D.Name
AND B.Value1= (SELECT MAX(C.Value1) FROM Table2 C where C.Name = B.Name)
AND D.Value1= (SELECT MAX(E.Value1) FROM Table2 E where E.Name = B.Name AND E.Value1 <B.Value1);

This SQL fetch the 2nd highest Value1 for every Name. The tables are:
Table 1
ID      Name      Value      Value2
1      John      1      54
5      Mike      5      78

Table 2
ID      Name      Value1      Comment
1      John      1      None
2      John      3      N
3      Mike      2      N
4      John      4      Bingo
5      Mike      5      Bingo

SQL result:
Name      Value1      Value2      Comment
John      3      54      N
Mike      2      78      N

The SQL is good but I couldn't build it in pplsoft.
LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 22728094
Peoplesoft is notorious for having poorly written sql statements with lots and lots of sub-selects that requery the same tables over and over again.

When the application itself won't support your syntax, it may be necessary to create a view with a good query then use people soft to write a simple query like

select * from your_view

as an alternate to the 4-way requery of table2,  try this...,  if I understand your intent, this should produce the same results you were looking for.

SELECT   name, value2, comment, value1
  FROM   (SELECT   a.name,
                   DENSE_RANK() OVER (PARTITION BY b.name ORDER BY b.value1 DESC) br
            FROM   table1 a, table2 b
           WHERE   a.name = b.name)
 WHERE   br = 2
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 22728128
hm..i'm sorry but I have no idea how to enter DENSE_RANK and PARTITION BY in peoplesoft. If you can see the attached image, this is the only way I can build a query bcos of the security setup.

It's very frustrating after I aggregated E.Value1 it doesn't allow me to add a criteria to that field anymore!
LVL 74

Expert Comment

ID: 22728143
that's why I suggest you try building a view,  either using your query, or mine.  Then use people soft to write a simple  select * from your_view.

If you don't have access to create a view,  you may need to request help from your dba.

Author Comment

ID: 22728252
not possible, asked the teams working on pplsft...was told request to build a view needs 3-6 months for approval. i was shocked nonetheless. so view is definitely out of the question.

any idea on how i can rewrite the sql so it can be easier to build in pplsft? thanks

LVL 74

Expert Comment

ID: 22730618
I don't mean creating a new view in the peoplesoft schemas.  But rather use your own schema with a view that queries into the peoplesoft schema tables.   Hopefully that wouldn't require a 3-6 month lag.

LVL 10

Assisted Solution

dbmullen earned 1000 total points
ID: 22731480
well..  the solution is what sdstuber says above..  create a view using the analytic..  

run the 2 queries in sqlplus
set autoexplain on

send the results to the DBA-group without the data.  They'll be happy to see a query that does less work.    

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question