Good afternoon all,

New to webfocs. Trying to take sql code that joins two tables using the TRIM function and convert to FOCUS. It looks like FOCUS does not allow it (FUNCTIONS included in the join clause). Is there another approach? Here is the SQL code:

SELECT BAN_ID, BAN_LAST_NAME, BAN_FIRST_NAME, SHP_FILTER
FROM BAN_SPRIDEN, SHP_USER_MST

WHERE TRIM(BAN_ID) = TRIM(SHP_ACCESS_ID

AND BAN_CHANGE_IND IS NULL
AND SHP_FILTER <> 'STU'
ORDER BY SHP_FILTER;

Thanks,

Douglas Dodd
douglas.dodd@tamuc.edu

Do you have an error message?

(or use a closing bracket after SHP_ACCESS_ID ?)

Try Something Like this

SET KEEPDEFINES=ON
DEFINE FILE JOBFILE
JCODE/A20 = TRIM('L', JCODE, 20, '$', 1, 'A20');
JOBID/A4 = JCODE|EDIT(JOBCODE,'$99');
END

JOIN FILE JOBFILE AT JOBID WITH JOBCODE TO ALL
FILE EMPFULL AT JOB_ID TAG M1 AS JW1
WHERE JOBCODE LIKE 'A__';
END