4 Replies Latest reply: Nov 20, 2012 11:32 AM by sharpe RSS

    Default Where

    sharpe
      Hi. I have a multi-record form. In the post-query trigger I calculate the Participants Local Time. I'd like to know if I can reference this Local Call Time value in the block's Where Clause (on the Property Sheet for the block) so that those records won't show up even if the user attempts to query every record via the form?

      Something like this perhaps:
      where to_char(:nbt_local_tm,'HH24:MI:SS') NOT between to_char(:nbt_best_call_tm_start,'HH24:MI:SS') and to_char(:NBT_BEST_CALL_TM_END,'HH24:MI:SS')
      If you know whether this is possible or if there is a different way to accomplish the same functionality I'd really appreciate the help.
        • 1. Re: Default Where
          user346369
          If you can calculate the value in a post-query trigger, you can probably create a stored function on the database to do the same calculation. Then your where clause can reference the stored function. You can pass values from your form (from a control block or form parameter), as well as values from the row being queried, to the stored function in the where clause.
          • 2. Re: Default Where
            sharpe
            Okay...my Post-Query actually just calls a stored database procedure:
            ttms.adjust_to_local_datetime(substr(:cc_call_lists.nbt_zip,1,5), sysdate, :cc_call_lists.NBT_LOCALDATETIME);       
            That returns the Local Time to the form item named :cc_call_lists.NBT_LOCALDATETIME).

            So are you saying I can somehow include this procedure in the block's Where Clause?
            • 3. Re: Default Where
              user346369
              Ok, assuming "ttms" is a package, you're half way done. Add a function to that package:
              Assuming pkg spec already has:
                Procedure adjust_to_local_datetime 
                   (zip in varchar2, sysdat in date, LocalTime out date);
              
              add this:
                Function Local_datetime(zip in varchar2, sysdat in date)
                  return date;
              
              Pkg body, add this:
                  Function Local_datetime(zip in varchar2, sysdat in date)
                    return date is 
                  Rtrn_datetime   date;
                Begin
                  Adjust_to_local_datetime(zip,sysdat,Rtrn_datetime);
                  Return Rtrn_datetime;
                End Local_datetime;
              Where clause can now have:
                where to_char(ttms.Local_datetime( substr(:cc_call_lists.nbt_zip,1,5)
                                                  ,sysdate )
                             ,'HH24:MI:SS')
                   NOT between to_char(:nbt_best_call_tm_start,'HH24:MI:SS') 
                           and to_char(:NBT_BEST_CALL_TM_END,  'HH24:MI:SS')
              With the above in place, you can use the function in another place. Remove the procedure call in the Post-Query, and let the database look up the NBT_LOCALDATETIME value. (Doing that is more efficient than calling the procedure from a Post-query trigger.)
              Change your column :cc_call_lists.NBT_LOCALDATETIME's Database Item property to Yes, and set Query Only to Yes, then set its Column Name property to:
              ttms.Local_datetime( substr(:cc_call_lists.nbt_zip,1,5) ,sysdate )
              • 4. Re: Default Where
                sharpe
                Awesome. I got it working thanks to your help. I really appreciate you taking the time.