This discussion is archived
4 Replies Latest reply: Nov 20, 2012 9:32 AM by sharpe RSS

Default Where

sharpe Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Awesome. I got it working thanks to your help. I really appreciate you taking the time.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points