RE: Is this possible with query in post_query trigger?

---

From: &nbsp;Alan Kong <kkkongee.cuhk.edu.hk>
Reply-To:&nbsp; ODTUG-SQLPLUS-Lfatcity.com
To: ; Multiple recipients of list ODTUG-SQLPLUS-L <ODTUG-SQLPLUS-Lfatcity.com>
Subject: &nbsp;Is this possible with query in post_query trigger?
Date:  Fri, 19 Sep 2008 02:30:39 -0800
Hi,
I have the following statements in my post-query trigger. The query returns correctly when the ONLY the :BUD_QRY.A_BUD_YR is not null. Nothing returned when the rest of other fields were not NULL. Can I have a query like it?

Thank you.
Regards
Alan


DECLARE
&nbsp; &nbsp; blk_id BLOCK;
&nbsp; &nbsp; final_qry varchar2(4000);
 ; &nbsp; center_qry varchar2(50);
 &nbsp;  costitem_qry varchar2(50);
 ; &nbsp; allo_qry varchar2(50);
 &nbsp;  req_by_qry varchar2(50);
begin
&nbsp;   ;blk_id :=Find_block('VW_REQLST08');

&nbsp; &nbsp; :tot.subtotal:=NULL;


 &nbsp; &nbsp;if :BUD_QRY.CENTER_ID is &nbsp;not null
 ; &nbsp; &nbsp; &nbsp; then &nbsp; &nbsp;   ; center_qry:=' and CENTER_ID= :BUD_QRY.CENTER_ID';
  ; &nbsp;end if;

if :BUD_QRY.costitem is not null
 ; &nbsp; &nbsp; &nbsp; then
 &nbsp;   ; &nbsp; costitem_qry:=' and cost_item_id = :BUD_QRY.costitem';
end if;

if :BUD_QRY.allo is not null
 ; &nbsp; &nbsp; &nbsp; then
 &nbsp;   ; &nbsp; allo_qry:=' and allo_id = :BUD_QRY.allo';
&nbsp; &nbsp; end if;
   ; &nbsp; if :BUD_QRY.req_by is not null
 ; &nbsp; &nbsp; &nbsp; then
 &nbsp;   ; &nbsp; req_by_qry:=' and req_by = :BUD_QRY.req_by';
&nbsp; &nbsp; end if;
   ; /* final_qry := 'BUD_YEAR= :BUD_QRY.A_BUD_YR ' || center_qry||costitem_qry||allo_qry || req_by_qry;*/

   ; select sum(subtotal) into :tot.subtotal from vw_reqlst08 where BUD_YEAR= :BUD_QRY.A_BUD_YR&nbsp; || center_qry||costitem_qry||allo_qry || req_by_qry;

end;





--
For more information on this topic or to become a member, visit our Web site at http://www.ODTUG.com

Join ODTUG for The PL/SQL and The APEX Expert Gathering of the Year! OPP2008 and APEXposed! 2008 will be held on October 29-30, 2008 at the Wyndham O'Hare, Chicago. Visit www.odtugopp.com or www.odtugapextraining.com for more details.


--
Author: Alan Kong
 ; INET: kkkongee.cuhk.edu.hk

Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGurufatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from).&nbsp; You may
also send the HELP command for other information (like subscribing).