Peoplesoft Query

Posted on 2008-10-15
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 73

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 73

Accepted Solution

sdstuber earned 250 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
                   DENSE_RANK() OVER (PARTITION BY ORDER BY b.value1 DESC) br
            FROM   table1 a, table2 b
           WHERE =
 WHERE   br = 2

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!
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 73

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 73

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 250 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

867 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now