I'm stumped! I've been looking at this too long I think. I am trying to create a temporary cursor of only those records in table (a) that have a record in table (b) AND whose table (b) field fontreq value =.t.
Here is the structure/relationship:
lcontrol table (a)
cn_name c(10) *--tag is name and is on UPPER(ALLTRIm(cn_name))
ctrlset table (b)
ctrlname c(10) *--tag is ctrlname and is on UPPER(ALLTRIM(ctrlname))
fontreq L *--always .t. or .f. tag is fontreq
lcontrol.cn_name has the same value as its related record in ctrlset.ctrlname
Again, I want to create a temporary cursor that contains the field cn_name from the lcontrol table but it should only contain records that have a matching record in ctrlset *AND* whose fontreq field is .T.
Here is what I've tried:
Example 1:
SELECT cn_name FROM lcontrol x WHERE ;
EXISTS (SELECT * FROM ctrlset y WHERE x.cn_name = y.ctrlname)
The result from example 1 is that I get ALL records from the lcontrol table for some reason
Example 2:
SELECT cn_name FROM lcontrol x WHERE ;
EXISTS (SELECT * FROM ctrlset y WHERE UPPER(ALLTRIM(x.cn_name)) = UPPER(ALLTRIM(y.ctrlname)))
The result from example 2 is that I get an error message "ERROR CORRELATING FIELDS"
If I were to do this using filters, it works correctly doing this:
SELE lcontrol
SET ORDER TO TAG NAME
SELE ctrlset
SET ORDER TO TAG CTRLNAME
SET FILTER TO ctrlset.fontreq=.T.
SELE lcontrol
SET RELATION TO cn_name INTO ctrlset
SET FILTER TO FOUND('ctrlset')
This shows the correct records, but I need a select statement so I can have a temporary cursor to use and also not disrupt the dataenvironment (relationships) already in place.
I've tried a BUNCH of other SQL statements, but I think I've just looked at this too much and its all blurry!!!!
Any ideas?
THANKS in advance,
Tracy
.·*´¨)
.·`TCH
(..·*
010000110101001101101000011000010111001001110000010011110111001001000010011101010111001101110100
"When the debate is lost, slander becomes the tool of the loser." - Socrates
Vita contingit, Vive cum eo. (Life Happens, Live With it.)
"Life is not measured by the number of breaths we take, but by the moments that take our breath away." -- author unknown
"De omnibus dubitandum"