Troublesome Database Join

[Russell Abbott]

I'm having problems joining two tables

task_time_worked - logs time records on tasks

u_employee_hours - imported values from external system showing hours paid for each employee

Here's the where clause

tw_sys_created_by = eh_u_active_directory_id

When I attempt to run the join, the view freezes my session. I'm guessing because the task_time_worked is so large?

Do I need to add the user table here and reference the user table from both task_time_worked and u_employee_hours?

[Zach Koch]

Make sure you have two == rather than one. Two is  used for comparing data, one is used for assigning data

[Brad Bowman]

Your Where clause looks fine, as long as u_active_directory_id is a string that matches User ID on sys_user.  If you suspect tak_time_worked is too large to form the view when joined, you can try adding something to the end of the Where clause like &&tw_sys_created_by='abel.tuter' to see if you get results, or whatever you can use to legitimately filter out time worked records that aren't needed (date range, etc).

[Russell Abbott]

This filter worked great for testing. Would I be able to filter on records created in the last 3 months (90 days)?

[Brad Bowman · Accepted solution]

I think that is too advanced for the Where clause, but you can try with a before Query Business Rule using the database view as the table.  Your script would look like this:

(function executeRule(current, previous /*null when async*/) {
	current.addEncodedQuery('tw_sys_created_on>=javascript:gs.beginningOfLast3Months()');
})(current, previous);

I don't have a large time worked table, joined to your custom table, so I don't know if the Business Rule will run before the Where clause churns away and locks the session.