Home » SQL & PL/SQL » SQL & PL/SQL » How to get multiple column values in a row (Oracle9i)
How to get multiple column values in a row [message #569647] Tue, 30 October 2012 11:53 Go to next message
rajkumar439
Messages: 2
Registered: October 2012
Location: Chicago
Junior Member
Here is my requirement..... I am pretty new to PL/SQL.

This is the procedure

CREATE OR REPLACE PROCEDURE ABCPROC.SP_ABC
(
XML IN CLOB,
P_refcursor OUT SYS_REFCURSOR
)
AS
BEGIN

Open P_refcursor for
SELECT CDH.I_AC AS ACCOUNT_NUMBER
,CORC.T_PROD_DATA AS COMMISION
,CLTS.T_PROD_DATA AS CONSUL
,CNTS.T_PROD_DATA AS CONTRA
FROM ABC_HRCHY CDH
,ABC_SVC CORC
,ABC_SVC CLTS
,ABC_SVC CNTS
WHERE LTRIM(RTRIM(CDH.I_AC)) IN
(
SELECT extractValue(VALUE(x),'/AcConSrc') ACCOUNT_NUMBER
FROM TABLE(xmlsequence(extract(xmltype(XML), '//Typidntsrc/AcConSrc'))) x
)
AND LTRIM(RTRIM(CORC.I_DATA_GRP)) = LTRIM(RTRIM(CDH.I_AC)) AND CORC.I_ABC_FIELD = '100'
AND LTRIM(RTRIM(CLTS.I_DATA_GRP)) = LTRIM(RTRIM(CDH.I_AC)) AND CLTS.I_ABC_FIELD = '101'
AND LTRIM(RTRIM(CNTS.I_DATA_GRP)) = LTRIM(RTRIM(CDH.I_AC)) AND CNTS.I_ABC_FIELD = '102'
;


END ;
/

For each account number (I_AC), we have multiple rows in ABC_SVC table. I want to return these values in the refcursor. The issue with above SQL program, the row is returning only when the account(I_AC) have values for all the products 100,101,102. If the row does not exist for one account, then the account row is returned with other products.

I want my output should be in the below format


ACCOUNT_NUMBER COMMISION CONSUL CONTRA
1 Y Y N
2 N N
3 N


Any help will be appreciated.
Re: How to get multiple column values in a row [message #569648 is a reply to message #569647] Tue, 30 October 2012 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
Since we don't have your tables or data, we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: How to get multiple column values in a row [message #569649 is a reply to message #569648] Tue, 30 October 2012 12:00 Go to previous messageGo to next message
rajkumar439
Messages: 2
Registered: October 2012
Location: Chicago
Junior Member
table ABC_SVC design is like below.
I_DATA_GRP I_ABC_FIELD T_PROD_DATA
01000 100 Y
01000 101 N
01000 102 Y
01001 101 Y
01002 102 Y
01003 100 N

[Updated on: Tue, 30 October 2012 12:01]

Report message to a moderator

Re: How to get multiple column values in a row [message #569650 is a reply to message #569649] Tue, 30 October 2012 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
Re: How to get multiple column values in a row [message #569653 is a reply to message #569647] Tue, 30 October 2012 12:42 Go to previous message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Previous Topic: Select query to get top two rows based on statusdate (merged 3)
Next Topic: Heterogeneous database integrated
Goto Forum:
  


Current Time: Wed Jul 30 05:25:28 CDT 2014

Total time taken to generate the page: 0.11531 seconds