Solved

Oracle query/function problem

Posted on 2014-01-06
7
739 Views
Last Modified: 2014-01-08
I have a table POL_DATA which has a foriegn key to another table POL_DATA_DETAILS...
POL_DATA_DETAILS has a primary key and the foreign key and has 2 columns POL_DATA_TITLE(VARCHAR2(100)) and POL_DATA_DESC(CLOB) that I need to return.
I am trying to query POL_DATA and JOIN POL_DATA_DETAILS to get the "multiple values" in the POL_DATA_TITLE and POL_DATA_DESC fields. SO obviously there are/can be multiple rows that exist in the Details table and I want to bring them back in a grid in my ASP app...

Ive done this before but I have forgot what I have done..I tried to create a function to bring both columns in the details back but I am failing

Select a.pol_title, a.pol_number, FUNC_POLICY_DETAILS(6), FUNC_POLICY_DETAILS_TEXT(6)
FROM POLICY_DATA a
Inner Join POLICY_DATA_DETAILS b ON a.POL_ID = b.POL_DATA_ID
Order By pol_number

Heres one function---
Select POL_DETAIL_TITLE into strTitle
FROM POLICY_DATA_DETAILS
Where POL_DATA_ID = nDetailID
Order By POL_DETAIL_ID;
   
RETURN strTitle;

Heres the error
ORA-01422: exact fetch returns more than requested number of rows

THANKS FOR ANY HELP!!!!
0
Comment
Question by:jknj72
7 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39760047
Can you post some sample data and your expected results?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39760048
you can return a collection (nested table) or aggregate them into single string

for example, modifying your function query to look something like this...


Select listagg(POL_DETAIL_TITLE,',') within group (order by pl_detail_id) into strTitle
FROM POLICY_DATA_DETAILS
Where POL_DATA_ID = nDetailID;

This assumes 11gR2  (11.2.0.1) or higher
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39760051
I don't understand the need for the function.  What is wrong with this:

Select a.pol_title, a.pol_number, b.POL_DATA_TITLE, b.POL_DATA_DESC
FROM POLICY_DATA a
Inner Join POLICY_DATA_DETAILS b ON a.POL_ID = b.POL_DATA_ID
where a.pol_id = 6
Order By pol_number
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:jknj72
ID: 39760146
sdstuber: I get this error when trying to compile the function
[Warning] ORA-24344: success with compilation error
11/45   PL/SQL: ORA-00923: FROM keyword not found where expected
11/1    PL/SQL: SQL Statement ignored
 (1: 0): Warning: compiled but with compilation errors

johnson: I had this previously and I actually think I can get it to work but I have a grid and I wanted to populate single values into the columns with single values and the functions were to bring back many values associated with the single values. I hope that makes sense...
I can use this if I cannot get the functions to work though.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39760164
<<  I had this previously and I actually think I can get it to work but I have a grid and I wanted to populate single values into the columns with single values and the functions were to bring back many values associated with the single values. I hope that makes sense...
I can use this if I cannot get the functions to work though.>>

As awking asked, explain with some sample data. That helps.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39760193
>>> 11/45   PL/SQL: ORA-00923: FROM keyword not found where expected

Do you have 11.2.0.1 or higher as noted above?

if not, try this...


Select
RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", POL_DETAIL_TITLE|| ',') order by pl_detail_id), '/x/text()').getstringval(),',')
 into strTitle
FROM POLICY_DATA_DETAILS
Where POL_DATA_ID = nDetailID;
0
 

Author Closing Comment

by:jknj72
ID: 39765441
Thanks sdstuber, its exactly what I needed
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

816 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

10 Experts available now in Live!

Get 1:1 Help Now