this is working fine but I want inner query ( various selects ) to be more reduce and efficient, please help
-- drop types in case...
drop type new_voch_tbl;
drop type new_voch_obj;
-- create Object Type
CREATE OR REPLACE TYPE new_voch_obj AS OBJECT (
GRNO NUMBER,
NAME VARCHAR2(100),
FATHER_NAME VARCHAR2(100),
BRANCH_NAME VARCHAR2(100),
SESSION_DESC VARCHAR2(100),
CLASS_NAME VARCHAR2(100),
SECTION_DESC VARCHAR2(100),
VOUCHER_NO VARCHAR2(100),
ISS_DATE DATE,
DUE_DATE DATE,
VALID_DATE DATE,
remarks VARCHAR2(100),
item_cost NUMBER
)
-- create Table Type of Object Type
CREATE OR REPLACE TYPE new_voch_tbl AS TABLE OF new_voch_obj;
CREATE OR REPLACE FUNCTION get_new_voch (pvochno in varchar2)
RETURN new_voch_tbl PIPELINED
IS
v_voch_tbl new_voch_obj;
BEGIN
## -:[ inner query → help needed for this query for the same results ]:-
for rec in (
select
(select MIN(PARTY_USER_CODE) from invoice_info@edulinks_253 where fee_voch_no = pvochno) GRNO,
(select party_name from parties@edulinks_253 where party_user_code = (select min(party_user_code) from invoice_info@edulinks_253 where fee_voch_no = pvochno)) NAME,
(select father_name from parties@edulinks_253 where party_user_code = (select min(party_user_code) from invoice_info@edulinks_253 where fee_voch_no = pvochno)) FATHER_NAME,
(select get_branch_name@edulinks_253(gl_doc_source) from parties@edulinks_253 where party_user_code = (select min(party_user_code) from invoice_info@edulinks_253 where fee_voch_no = pvochno)) BRANCH_NAME,
(select get_session_desc@edulinks_253(session_id) from parties@edulinks_253 where party_user_code = (select min(party_user_code) from invoice_info@edulinks_253 where fee_voch_no = pvochno)) SESSION_DESC,
(select get_class_name@edulinks_253(class_no) from parties@edulinks_253 where party_user_code = (select min(party_user_code) from invoice_info@edulinks_253 where fee_voch_no = pvochno)) CLASS_NAME,
(select get_section_desc@edulinks_253(section_id) from parties@edulinks_253 where party_user_code = (select min(party_user_code) from invoice_info@edulinks_253 where fee_voch_no = pvochno)) SECTION_DESC,
(select max(fee_voch_no) from invoice_info_det@edulinks_253 where fee_voch_no = pvochno) VOUCHER_NO,
trans_id,
(select min(to_char(ISS_DATE, 'YYYY-MM-DD')) from invoice_info@edulinks_253 where fee_voch_no = pvochno) ISS_DATE,
(select min(to_char(DUE_DATE, 'YYYY-MM-DD')) from invoice_info@edulinks_253 where fee_voch_no = pvochno) DUE_DATE,
(select min(to_char(VALID_TILL, 'YYYY-MM-DD')) from invoice_info@edulinks_253 where fee_voch_no = pvochno) VALID_DATE,
remarks,
item_cost
from trans@edulinks_253 t
where trans_id in (SELECT trans_id from invoice_info_det@edulinks_253 where fee_voch_no = pvochno)
##-:[ innert query → End: help needed for this query for the same results ]:-
--and 'Y' = get_access_token(:ptoken)
)
loop
PIPE ROW(new_voch_obj(
rec.GRNO,
rec.NAME ,
rec.FATHER_NAME ,
rec.BRANCH_NAME ,
rec.SESSION_DESC,
rec.CLASS_NAME ,
rec.SECTION_DESC,
rec.VOUCHER_NO ,
rec.ISS_DATE ,
rec.DUE_DATE ,
rec.VALID_DATE ,
rec.remarks ,
rec.item_cost
));
end loop;
END get_new_voch;
# now test this Pipeline table function...
select * from TABLE (get_new_voch('145938'));
.