Question on queries in VFP 5.0a 
Author Message
 Question on queries in VFP 5.0a

Hi

I have two questions on queries as follows:

Can I write a query so that I look at all of the fields in the table
not just one?

i.e
SELECT * FROM invt;
        WHERE all fields = 'Screw'

and secondly

Can I write a query that looks for a sub string in a string.

i.e.

SELECT * FROM invt;
        WHERE fieldname contains 'screw'

so that this query would return the record 2 1/2' Wood{*filter*}

Thanks in advance

Nick Steele



Sun, 09 Apr 2000 03:00:00 GMT  
 Question on queries in VFP 5.0a

Quote:
>> SELECT * FROM invt;

       WHERE all fields = 'Screw' <<

Nick

If there are just a few fields you can AND them:
 WHERE field1='Screw' AND field2='Screw' AND field3 = 'Screw' ...

If there's more fields than this that begin with 'Screw' you table
design is screwed up.  

Any way a VFP query on VFP tables with lots of fields like this, say
field 5 through 37, could perhaps be handled quicker in a loop to spot
the odd one:

 SCAN
  SCATTER TO aTempArray
  FOR i = 1 to 5
    IF EVAL(FIELD(i)) <> 'Screw'
       ? 'Column',FIELD(i), '- Row',RECNO()
       LOOP
     ENDIF
  NEXT
 ENDSCAN

<<SELECT * FROM invt;
       WHERE fieldname contains 'screw'

so that this query would return the record 2 1/2' Wood Screw>>

 ... WHERE UPPER(field3) LIKE "%SCREW%"
 or WHERE UPPER(field3) LIKE "% SCR_W%" if you only want 'Screw'  after
a space and will accept a misspelt ' Scrww'

Have you thought about a table like this?

 prodno size   type article package stock

 12345   2.50  wood  screw  200     234
 12247   1.75  wood  screw  100      23

SELECT ' FROM Products WHERE article = 'screw' and type = 'wood'
 and size BETWEEN 1.00 and 3.00

-Anders

!^NavFont02F03640011MGJHGpMHBDHIfHJ65B782

--

10/24/97 22:28
---------
Using: OUI 1.8 Beta 6 from http://www.dvorak.com



Tue, 11 Apr 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Follow up question to previous thread on VFP 5.0a query questions

2. Follow up question to previous thread on VFP 5.0a query questions

3. Question about VFP 5.0 and VFP 5.0a

4. To 5.0a or not to 5.0a, that's the question

5. A few Questions on VFP 5.0a

6. VFP 5.0a Questions

7. VFP 5.0a: General Questions

8. VFP 5.0a - one more resize related question

9. DragIcon Question VFP 5.0a

10. VFP Query Logic Question

11. VFP alters my query! (newbie question)

12. VFP Query-by-Form question

 

 
Powered by phpBB® Forum Software