Most of the time I ask questions and never provide. I know that many of you are "Power RBase" developers and you might turn an eye on this code but, it is working like a charm and I wanted to share it with you. What I had to do was to get the last name out of the full name and then check another table to find anyone with the last name using a CONTAINS statement. The main problem was in the area of a name like "WARD". I was getting all kinds of WARD (Hayward, Ward, Smithward, and ....). So I used an SGET in the WHERE clause after I extracted the first character of the Last Name. I now delimit to anyone with the Last Name containing Ward and nothing more.
Phil
============================================================================
======
-- ========Check NCA Data for Direct Dial Numbers Only ======
COMPUTE vmany2 AS COUNT nca_id FROM usr WHERE dir_dial_nbr = .vp4 AND
mstr_rcd_typ = 'I'
IF vmany2 = 0 THEN
WRITE ' This CD has no individual match in NCA ten digit phone'
WRITE ' Checking for Name Equivalence on NCA '
-- Get the names related to this CD Record from NCA
-- Finds the position of the comma
SET VAR vsloc = (SLOC (.vp3, ','))
-- Increment the position by 2 to get first letter of first name
SET VAR vsloc = (.vsloc + 2)
-- Get the First Letter of the First Name
SET VAR vsget = (SGET(.vp3,1,.vsloc))
-- Extract the Last name from the full name
SET VAR vvncanm = (SSUB(.vp3,1))
-- Put it all together for the Search variable
SET VAR vsch = ((.vvncanm + ',' + ' ') + .vsget)
-- Get the first letter of the last name
SET VAR v1st = (SGET(.vp3,1,1))
DECLARE c5 SCROLL CURSOR FOR SELECT
nca_id,comp_cc_cd,nca_nm,dir_dial_nbr,prim_ext_nbr +
FROM usr WHERE mstr_rcd_typ ='I' AND nca_nm CONTAINS .vsch and
(SGET(nca_nm,1,1)) = .v1st
-- No Related Names Found in NCA
IF SQLCODE = 100 THEN
WRITE 'There were no associated names in NCA related to this CD
Record'
ENDIF
-- Write the Related Records found in NCA for this CD Record
OPEN c5
FETCH NEXT FROM c5 INTO vnc51, vnc52, vnc53, vnc54,vc5primeext
WHILE SQLCODE <> 100 THEN
WRITE 'NCA=> ','ID: ',.vnc51,'COMP/CC =>',.vnc52,'Number =>
',.vnc54, 'NAME => ',.vnc53
FETCH NEXT FROM c5 INTO vnc51, vnc52, vnc53, vnc54,vc5primeext
ENDWHILE
CLOSE c5
DROP CURSOR c5
ELSE
WRITE 'Continue Analysis - Search NCA by CD ten digit '
ENDIF
winmail.dat
Description: application/ms-tnef
