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

x
?
Solved

Getting a specific number of records from Oracle DB with Coldfusion

Posted on 2016-10-05
6
Medium Priority
?
104 Views
Last Modified: 2016-10-05
Good morning All!!! I have a dilemma that I was hoping I could get some help with. I have an Oracle database, with two specific tables I'm dealing with. The first table is a list of various projects, and has the project number (PROJ_NO) as a distinct field. My second table has rows with information about the projects in the first table, but there isn't a one to one or even one to may correlation. So I could have projects in the first table that has no corresponding record in the second table, or I could have one record in the first table with multiple corresponding records in the second table. So in my query I used a left join of the second table to the first, and my results are correct, giving me all projects, whether a project number appears in the second table or not. The problem I have is that for a given project number, I could have 10 or more records in the second table and I only want the latest 3 records. I do record the date that the info is inserted into the second table. I thought I could use "FETCH NEXT 3 ROWS ONLY" but that doesn't appear to work correctly in a left join. On the ColdFusion side, I'm using the CFOUPUT query to display my results. Am I wrong to try to use the FETCH NEXT 3 ROWS ONLY?? It appears it's trying to get the next three project numbers rather than the three comments about the projects from the second table.

<CFQUERY DATASOURCE="MCA_Oracle" NAME="Input">
SELECT current_bulk_filter.PROJ_NO,
X.PROJ_CLOSED_REASON
FROM MCA.current_bulk_filter

LEFT JOIN
(SELECT spend_plan_history.PROJ_NO,
spend_plan_history.PROJ_CLOSED_REASON FROM
MCA.spend_plan_history

ORDER BY spend_plan_history.IDA DESC
FETCH NEXT 3 ROWS ONLY) X
ON x.proj_no = current_bulk_filter.proj_no

where current_bulk_filter.proj_no = X.PROJ_NO
</CFQUERY>




<TABLE>

<cfset projectNo = 0>
<cfoutput query="Input" group="PROJ_NO">
<cfset projectNo = projectNo + 1>

<TR>

<TD><CFINPUT TYPE="TEXT"
             NAME="PROJNO#projectNo#"
             VALUE="#PROJ_NO#"
             SIZE="3"
             MAXLENGTH="35"
             style="font-size:12px; background-color:white"></TD>
             
             
<TD><textarea rows="8"  cols="35 name="PROJ_CLOSED_REASONP#projectNo#" id="PROJ_CLOSED_REASONP" style="font-size:12px; background-color:white"><CFOUTPUT>#PROJ_CLOSED_REASON# // </CFOUTPUT></textarea></TD></TR></cfoutput></TABLE>
0
Comment
Question by:diecasthft01
[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
  • 3
  • 2
6 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 41830018
I believe that this should get you pretty close to what you are looking for:
SELECT proj_no, 
       proj_closed_reason 
FROM   (SELECT current_bulk_filter.proj_no, 
               mca.proj_closed_reason, 
               Row_number() 
                 over ( 
                   PARTITION BY current_bulk_filter.proj_no 
                   ORDER BY mca.ida DESC) rn 
        FROM   mca.current_bulk_filter 
               left join mca.spend_plan_history 
                      ON ( current_bulk_filter.proj_no = mca.proj_no )) 
WHERE  rn <= 3; 

Open in new window

0
 

Author Comment

by:diecasthft01
ID: 41830129
Yup...that gets my results that I expected to get. I totally missed the idea of partition. Am I able to add another LEFT JOIN to this query?? I actually have a few LEFT JOINS from different tables, and just left them out of the example for clarity.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41830144
That shouldn't be a problem at all.  You may want to do it after the row number so that you aren't joining all the rows and then cutting some off.  Extra work you don't need to do.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:diecasthft01
ID: 41830163
Got ya!!!! Thanks A lot!!! I believe this is exactly what I needed.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41830169
Slight variation -
select a.proj_no, b.proj_closed_reason
from mca.current_bulk_filter a
left join
(select proj_no, proj_closed_reason,
 row_number() over (partition by proj_no order by ida desc) rn
 from mca.spend_plan_history) b
on a.proj_no = b.proj_no
where b.rn <= 3;
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41830189
I didn't think that doing the row limitation first would be helpful because either way it has to go through all the rows in the child table.  In fact, it could be longer because you have to go through them once to limit the rows and then a second time on the smaller set to do the join.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

670 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