I would consider myself a beginner with SQL and was trying to create multiple SQL calls and then join the results with the List function. If I could make them all with one SQL call that would be great (& I'm pretty sure it would be possible), but wondered if the performance is better by doing a couple smaller calls since the tables are fairly large?
I am trying to create a list of open jobs that an employee:
- has worked on in the past
- is a field (or office) overhead job (whether or not the employee has recorded time against it yet)
- has been designated as the leadman of (but may not have recorded time against it yet)
I first made a call to set a variable ($jobsOpen) with the primary key of all the open jobs using SQL. I also have SQL statements working to find the open overhead jobs.
I found another forum thread suggesting that using IN versus INNER JOIN had better performance and developed the SQL statement below to find the open jobs the employee has worked on before, but am getting a syntax error. Can anyone spot what may be causing the error? I added the substitute to the @openJobs variable based on what I could understand from the w3schools page, but that didn't seem to fix the error. Thank you for your guidance!
@openJobs = Substitute($jobsOpen; "¶"; ", ");
@targetTableName = SQL_GTN(TIMESHEETLINES::ID_timesheetlines__pk);
@ID_employee = $ID_employees;
"SELECT " & SQL_GFN(TIMESHEETLINES::ID_oldjob__fk) & "
FROM " & @targetTableName & "
WHERE " &
SQL_GFN(TIMESHEETLINES::ID_employees__fk) & "=? AND " &
SQL_GFN(TIMESHEETLINES::ID_oldjob__fk) & " IN (" & @openJobs & ")";
@result = ExecuteSQL( @query;
"|"; // field separator
"¶"; // row separator