This content has been marked as final. Show 4 replies
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.1 person found this helpful
Okay...my Post-Query actually just calls a stored database procedure:
That returns the Local Time to the form item named :cc_call_lists.NBT_LOCALDATETIME).
ttms.adjust_to_local_datetime(substr(:cc_call_lists.nbt_zip,1,5), sysdate, :cc_call_lists.NBT_LOCALDATETIME);
So are you saying I can somehow include this procedure in the block's Where Clause?
Ok, assuming "ttms" is a package, you're half way done. Add a function to that package:
Where clause can now have:
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;
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.)
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')
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 )
Awesome. I got it working thanks to your help. I really appreciate you taking the time.