Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to reduce query size

SmithJohn452 days ago — edited 2 days ago

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'));
.
This post has been answered by Steve Muench-Oracle on Oct 1 2025
Jump to Answer
Comments
Post Details
Added 2 days ago
6 comments
94 views