Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Peoplesoft Query

Posted on 2008-10-15
10
Medium Priority
?
1,052 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 Comments
 
LVL 74

Expert Comment

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

0
 

Author Comment

by:jannhan
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.
0
 
LVL 74

Accepted Solution

by:
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,
                   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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jannhan
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!
untitled.JPG
0
 
LVL 74

Expert Comment

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

Author Comment

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

0
 
LVL 74

Expert Comment

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

0
 
LVL 10

Assisted Solution

by:dbmullen
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
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

721 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