Oracle query/function problem

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!!!!
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
Can you post some sample data and your expected results?
sdstuberCommented:
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
johnsoneSenior Oracle DBACommented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jknj72Author Commented:
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.
SharathData EngineerCommented:
<<  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.
sdstuberCommented:
>>> 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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
Thanks sdstuber, its exactly what I needed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.