RE: Is this possible with query in post_query trigger?

Fernando, you are right, I noticed the first problem and
didn't look at the final syntax.
You do have some dynamic sql in Forms.  You can stuff this
into the where property in a block, or you can use a from
clause query.
 
If you don't like either of those, pass the entire string to
a function in the DB that runs the sql and passes back the
value. If you do this a lot then bind vars would be smart.
 
Given that you are putting this in a post query trigger, are
you aware that this will fire for every row returned in the
block?  
 
Paul Dorsey
Dulcian, Inc.
pdorseydulcian.com
732 744 1116 x110
 

________________________________

Sent: Fri 9/19/2008 6:55 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L



>From what I see here, you're trying to dynamically
construct a SQL
statement. You cannot do this from the forms side. If you
really want to
use dynamic SQL I encourage you to write something on the
database end
like a package function that returns back your count value.
Be sure to
use bind variables when constructing your query so you don't
fill the
shared pool with unbound statements.

The database will have a higher version of PL/SQL than what
is running
in forms and so you are free to use every feature at your
disposal.
Encapsuling it in a database package shields the forms from
the
implementation. All you're doing is calling a function that
returns a
count. The form doesn't have to know how you did it.



-----Original Message-----
Alan Kong
Sent: Friday, September 19, 2008 3:31 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L

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
    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';
    end if;

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

 if :BUD_QRY.allo is not null
        then
        allo_qry:=' and allo_id = :BUD_QRY.allo';
    end if;
    
  if :BUD_QRY.req_by is not null
        then
        req_by_qry:=' and req_by = :BUD_QRY.req_by';
    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: Fernando Luna
  INET: Fernando.Lunatideworks.com

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).