Solved

Oracle query/function problem

Posted on 2014-01-06
7
722 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 31

Expert Comment

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

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jknj72
Comment Utility
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
Comment Utility
<<  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
Comment Utility
>>> 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
Comment Utility
Thanks sdstuber, its exactly what I needed
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

13 Experts available now in Live!

Get 1:1 Help Now