Solved

Peoplesoft Query

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

Accepted Solution

by:
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
  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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 73

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 73

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to update the value of duplicated records (except latest one) 2 20
sql server insert 12 30
Access 2003 query lost it's only join 7 27
VB.net and sql server 4 36
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

778 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