Focal Point
joinin on field using TRIM FUNCTION

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7081057331/m/4807031486

October 04, 2016, 02:25 PM
ddoddjr
joinin on field using TRIM FUNCTION
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


8.10.5M
Linux 64-bit
October 05, 2016, 04:34 AM
MichaelBalle
Do you have an error message?

(or use a closing bracket after SHP_ACCESS_ID ?)


WebFOCUS 7.6, 7.7
Windows, All Output formats
October 13, 2016, 10:50 PM
Satya Dach
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


WebFOCUS 8.1
Windows, PPT