Re: Is this possible with query in post_query trigger?

Dear Paul,
Thank you very much for your help.
They are all text field. Sorry I should have mentioned.
I just replaced the codes, it still not returning the value.
I will try 
again on Monday.

Regards
Alan

On 9/19/2008 7:10 PM, Paul Dorsey wrote:
> 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).
>
>   
-- 
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).