Re: sql syntax
In article <71i47r$ib5$1@nnrp1.dejanews.com>, <mjgator@sibretown.com> wrote:
>Two tables...A and B. Table A has a character field with words: CAT DOG
>ETC. Table B has a longer character field with words: SPONGE FISHERMAN
>OWNER'S OF DOGS etc. I want set up a foreach which goes through table B and
>if it finds Table A entries, continue foreach or delete the record or
>whatever. So it would throw out record #2 in table B "OWNER's OF DOGS"
>because of the entry in Table A "DOG."
>
>Since this is not an exact match which would be easy, I have tried all
>wildcard permutations I can think of with match, like, etc. Nada. Is there
>an efficient way to do this?
How about stepping through all the Table A entries and looking for matches
in table B, something like this:-
database nsoe
main
define p_keyword, p_keyword_plus char(12)
define m_phrase char(50)
create temp table A ( keyword char(10) ) ;
insert into A values ( 'CAT' ) ;
insert into A values ( 'DOG' ) ;
create temp table B ( phrase char(50) ) with no log ;
insert into B values ( 'SPONGE' ) ;
insert into B values ( 'FISHERMAN' ) ;
insert into B values ( 'OWNERS OF DOGS' ) ;
declare c_keyword cursor for select keyword from A
foreach c_keyword into p_keyword
let p_keyword_plus = "*", p_keyword clipped, "*"
delete from B where B.phrase matches p_keyword_plus
display p_keyword, " caused ", sqlca.sqlerrd[3], " delete(s) from B"
end foreach
end main
which returns this:-
CAT caused 0 delete(s) from B
DOG caused 1 delete(s) from B
We construct "*CAT*" from "CAT" and then match against "*CAT*",
seems to work. Is that the sort of thing you have in mind?
- Paul