Solved

Oracle query/function problem

Posted on 2014-01-06
7
754 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
[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
7 Comments
 
LVL 32

Expert Comment

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

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 35

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 41

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 74

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
A question about syntax 5 50
replicate in oracle 13 41
User Prompt in Vertica SQL (vsql) 1 26
emailing registered email addresses in phpmyadmin 5 47
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

740 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