Hello Experts,
we have a requirement as mentioned below with all the scripts,
Scripts:
create table a1 (sr_no number, emp_name varchar2(20),code varchar2(30))
/
create table b1 (sr_no number, code varchar2(30),seq_number varchar2(300))
/
create table c1(sr_no number, code varchar2(30),seq_number varchar2(300), amount number)
/
insert into a1 values(1,'ABC','P09')
/
insert into a1 values(2,'DEF','P88')
/
insert into b1 values(1,'P09',100)
/
insert into b1 values(2,'P88',5100)
/
insert into b1 values(2,'P88',8100)
/
insert into c1 values(1,'P09',100,1000)
/
insert into c1 values(2,'P88',5100,500)
/
insert into c1 values(2,'P88',8100,1500)
/
commit
/
Anonymous Block:
DECLARE
l_code VARCHAR2 (2);
l_seq_num VARCHAR2 (300);
l_amount NUMBER;
BEGIN
SELECT 'Y',
LISTAGG (b1.seq_number, ',') WITHIN GROUP (ORDER BY a1.code)
"Values"
INTO l_code, l_seq_num
FROM a1, b1
WHERE a1.sr_no = b1.sr_no AND a1.code = 'P88';
DBMS_OUTPUT.put_line ('l_code: ' || l_code);
DBMS_OUTPUT.put_line ('l_seq_num: ' || l_seq_num);
SELECT (1 / SUM (c1.amount))
INTO l_amount
FROM b1, c1
WHERE b1.code = c1.code
AND c1.seq_number IN (DECODE (l_code, 'Y', l_seq_num, 100))
and c1.code = 'P88';
DBMS_OUTPUT.put_line ('l_amount: ' || l_amount);
END;
not able print or get the value for l_amount.
please suggest.
Thanks