• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • Last Modified:

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!!!!
0
jknj72
Asked:
jknj72
1 Solution
 
awking00Commented:
Can you post some sample data and your expected results?
0
 
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
0
 
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
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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.
0
 
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.
0
 
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;
0
 
jknj72Author Commented:
Thanks sdstuber, its exactly what I needed
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now