Solved

Peoplesoft Query

Posted on 2008-10-15
10
1,041 Views
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?
0
Comment
Question by:jannhan
  • 4
  • 3
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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?

0
 

Author Comment

by:jannhan
Comment Utility
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.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
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,
                   a.value2,
                   b.comment,
                   b.value1,
                   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
0
 

Author Comment

by:jannhan
Comment Utility
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!
untitled.JPG
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.
0
 

Author Comment

by:jannhan
Comment Utility
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

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.

0
 
LVL 10

Assisted Solution

by:dbmullen
dbmullen earned 250 total points
Comment Utility
well..  the solution is what sdstuber says above..  create a view using the analytic..  

run the 2 queries in sqlplus
set autoexplain on
http://asktom.oracle.com/tkyte/article1/autotrace.html

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to recover a database from a user managed backup

762 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

11 Experts available now in Live!

Get 1:1 Help Now