Formula column help

[shekhar12]

Dear all.............

My requirement is followimg

i m using cursor in the formula column ,my cursor is returning more than 1 rows like
as
aw
sh
qs.

I have to return only one row to report as as,aw,sh,qs and so on.

So that my formula column will return only one row.

my cursor is as following.


=============


SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE test
2 AS
3 --c1rec c1%ROWTYPE;
4 CURSOR c1 IS
5 SELECT DISTINCT CUSTOMER_ITEM_NUMBER
6 FROM MTL_CUSTOMER_ITEM_XREFS_V mciv
7 WHERE
8 mciv.customer_number IN (
9 SELECT DISTINCT RA.CUSTOMER_NUMBER
10 FROM RA_CUSTOMER_TRX_ALL RCTA,
11 RA_CUSTOMER_TRX_LINES_ALL RCTLA,
12 RA_CUSTOMERS RA,
13 mtl_customer_items mci
14 WHERE RCTA.TRX_NUMBER='200600019'--:invnum
15 AND RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
16 AND mci.customer_item_id=RA.CUSTOMER_ID
17 AND RA.CUSTOMER_NAME ='BAJAJ AUTO LIMITED (CHAKKAN)'
18 AND RCTA.ORG_ID=RCTLA.ORG_ID
19 AND RCTLA.LINE_TYPE='LINE'
20 AND RCTA.BILL_TO_CUSTOMER_ID=RA.CUSTOMER_ID
21 AND RCTLA.inventory_item_id IN (SELECT DISTINCT msi.inventory_item_id
22 FROM mtl_system_items msi,
23 RA_CUSTOMER_TRX_LINES_ALL RCTLA1,
24 ra_customer_trx_all rcta1
25 WHERE rcta1.trx_number='200600019'
26 AND RCTA1.CUSTOMER_TRX_ID=RCTLA1.CUSTOMER_TRX_ID
27 AND rctla1.inventory_item_id=msi.inventory_item_id))
28 AND mciv.inventory_item_id IN (SELECT DISTINCT msi.inventory_item_id
29 FROM mtl_system_items msi,
30 RA_CUSTOMER_TRX_LINES_ALL RCTLA1,
31 ra_customer_trx_all rcta1
32 WHERE rcta1.trx_number='200600019'
33 AND RCTA1.CUSTOMER_TRX_ID=RCTLA1.CUSTOMER_TRX_ID
34 AND rctla1.inventory_item_id=msi.inventory_item_id);
35 BEGIN
36 for c1rec in c1
37 loop
38 dbms_output.put_line(c1rec.CUSTOMER_ITEM_NUMBER||',');
39 END LOOP;
40* END;
SQL> /

Procedure created.

SQL> exec test;
DH121040,
DH121097,
DH121123,
DJ121008,
DJ121020,
===========


My requirement is (DH121040,DH121097,DH121123,DJ121008,DJ121020)

regards
Shekhar

shekhar

[admin]

You will have to move the values to a variable with concatenation,

My_variable := My_variable || ',' || c1rec.CUSTOMER_ITEM_NUMBER;

38 dbms_output.put_line(c1rec.CUSTOMER_ITEM_NUMBER||',');

Move line 38 after 39 as below,

39 END LOOP;
38 dbms_output.put_line(My_variable);

Hope will resolve your issue.

[shekhar12]

dear friend
Thank you

Regards
Shekhar

shekhar