RE: Is this possible with query in post_query trigger?

Alan,
 
This should have been posted to the Developer list.
 
Your error is that you are hard coding in your references as
values.
 
I corrected your code below.  I assume id is a number and
req_by is text.
 
Always stuff your query into a text field and then print it
out so you can see if you have built the string correctly.
 
Paul Dorsey
Dulcian, Inc.
pdorseydulcian.com
732 744 1116 x110
 


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

    :tot.subtotal:=NULL;


    if :BUD_QRY.CENTER_ID is  not null
        then
        center_qry:=' and CENTER_ID= '||:BUD_QRY.CENTER_ID; 
 --- fix 1
    end if;

 if :BUD_QRY.costitem is not null
        then
        costitem_qry:=' and cost_item_id =
'||:BUD_QRY.costitem;   --- fix 2
 end if;

 if :BUD_QRY.allo is not null
        then
        allo_qry:=' and allo_id = '||:BUD_QRY.allo;  --- fix
3
    end if;
    
  if :BUD_QRY.req_by is not null
        then
        req_by_qry:=' and req_by =
'''||:BUD_QRY.req_by||'''';   --- fix 4, note the added
single quotes
    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  ||
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). 
You may
also send the HELP command for other information (like
subscribing).