Foxpro Compare or SQL Query

Can we have sample data from original and enhanced fields so we know what format they are in? Table structure and fieldnames will be helpful

Meanwhile you can use:

Select originalfield, newfield from tablename where StrCmp(originalfield,newfield) <> 0

That will return both fields if they are different and then you could decide what is missing.

Here is an example:

Field name
Original Description:
BUSH BRONZE FLANGED 16MM X 22MM X 24 LONG

Descript
BEARING, SLEEVE, FLANGED, 16 MM ID, 22 MM OD, 24 MM LG

In this scenario we lost the word BUSH.

Hope that helps,

Did you not lose BRONZE, X and X ?

I just want to understand better.

YES you are right we lost both bronze, bush, and we could lose the 'X' signs because we separate them dimensions by comma's

I just did an ASP script that could automate the process of finding missing words and updating table accordingly and was going to post before you posted this. So I want to know if you could use ASP.

If you know and use ASP, you can use the following script to achieve what you want and automatically make the necessary updates. I have not tested this on any database, so first make a backup of your table before trying it out.

I hope you have the correct connection string to connect to your database. Also I hope the code doesn't look too untidy.
<%
Dim oldArray, newArray, iLoop, jLoop, found, missingStr, rs, rsUpd, conn

Set conn = Server.CreateObject("ADODB.Recordset")
conn.Open("") 'Put your connection string in the quote in the bracket.
Set rs = Server.CreateObject("ADODB.Recordset")
Set rsUpd = Server.CreateObject("ADODB.Recordset")

rs.Open "Select id, oldfield, newfield from tablename where strcmp(oldfield,newfield) <> 0",conn
      
Do While Not rs.eof
   oldArray = Split(rs("oldfield")," ")
   newArray = Split(rs("newfield")," ")
   missingStr = " "

   For iLoop = LBound(oldArray) to UBound(oldArray)
      found = "false"
      For jLoop = LBound(newArray) to UBound(newArray)
           If Lower(cStr(oldArray(iLoop))) = Lower(cStr(newArray(jLoop))) Then
            found = "true"
          End If
      Next
      If found = "false"      'word is missing
         missingStr = missingStr & oldArray(iLoop)
      End If
   Next
   newcorrectStr = rs("newfield") & missingStr
   rsUpd.Open "Update tablename set newfield = '" & newcorrectStr & "' where id = '" & 

rs("id") & "'",conn
rs.MoveNext
Loop
%>

If you want to use the above script, you'd have to increase the server's script timeout.
Server.ScriptTimeout = 9000 'or something put that at the beginning of the asp file

Unfortunately we do not have the ASP program.  Sorry about that.

And when I use this script Select originalfield, newfield from tablename where StrCmp(originalfield,newfield) <> 0 in SQ it tells me that the StrCmp is not a known command?

Oh okay, you are using Foxpro... I assumed MySQL because of the tags in your question... Just a minute

ya sorry.  Fox or SQL

I have a tool that can convert foxpro to SQL, would you need that?

2ndly: I don't know if this is allowed in EE... If you could give me your fox db so I'd use ASP to do that for you. Or you could install IIS (it shouldn't take long) and afterwards the ASP script would work on your system.

http://www.alvechurchdata.co.uk/foxtext.htm

i will try to download it and see if I can do it.  I will let you know,
Thanks for your help

That was just a link to some foxpro function. I have attached the dbf2sql program here. Rename as directed
dbf2sql.txt

Nothing seemed to work.  Does anybody have a query in SQL that I could run to achieve the results I need?

AFAIK there is no way to do what you want using SQL. It requires some programming. One of us could rewrite the ASP code in VFP.

What defines a "word"? I assume a sequence of alphanumeric characters (so anything that is not in a-z or A-Z or 0-9 would mark the start or end of a word. Is this true?

How do you want the results reported?

Ideally I would like the corp_enh column to be flagged with an X for every item where there are extra words in the orig_desc that are not in the descript fields.  And yes a sequence of alphanumeric characters would mark the start or end of a word

Since today is 4/15 I will focus first on tax filing. Maybe someone else can jump in.

"corp_enh column to be flagged with an X". Be more specific. From your original description there are only 2 columns, and we don't know their names. Is corp_enh one of these columns or are there really more than 2?

Do you want corp_enh set = "X" or "X" added to it and if so how.

Also please note that the more you can tell us about your situation the easier it is to help.

Also I don't understand why you wanted a "query", or what you mean by "query".

I want to compare two columns in a database that has 7870 records, there are many fields in this database but the two that I need to compare are the Orig_desc field against the Descript field
Here is an example:
Orig_desc field says "BEARING YSA208-2FK MSR" and the Descript field says "BEARING, BALL, RE-LUBE, MSR"

I need to find all of the instances where words from the Orig_desc field to no appear anywhere in the descript field.  Therefore in this example it would be "ysa208-2fk"  I do not need these words highlighted or anything, what I would like is when there are words missing for the field "corp_enh" to be flagged with an "X" or something so that we can concentrate on these items where we now there are missing words.  Does that make more sense?

The term query is probably not the right term for this case.  I am just a beginner and in the past when I have compared two columns I have run queries in SQL
                                                                                                                                                                                                                                                                                                                                                                                                                     

Thanks. In the original question you said "There are over 100,000 lines in this database" which I interpreted to mean "records". Now you say "7870 records".

I guess the number does not matter, but it is confusing.

I still do not know what "flagged with an X" means. Do you want corp_enh to be set to X or do you want X added to whatever is already there, and if the latter, how do you want it added?

Did you not see this question in my previous post? Please read my posts thoroughly and respond to each item.

sorry, I should have mentioned that I isolated only the records that I need to check.  The database that I am now working with only has 7800 lines which I thought made it easier than dealing with the entire 100,000 records.

I thought I did answer this question in my previous post.  I said that there is a column named Corp_enh in my table.  When the program is run then I would like something to go in that column to flag us to look at those lines.

Listen, I am doing my best here to provide you the info you need.  I am only a beginner, which I thought was the majority of the people on here asking for help.  I don't know what terminology is appropriate or how to describe things appropriately.  If you cannot help me then that is fine, but I do not see the need to be harsh or make me feel like a child.  I obviously was trying to answer all of your posted questions, if I did not answer it appropiately perhaps it is because I am not understanding what you are asking?

Sorry - I don't mean to stimulate uncomfortable feelings. I do get frustrated when dealing with a lot of people who don't express their requirements completely or accurately. So I guess you are getting a bit of my frustration.

Regarding Corp_enh - I'm trying to determine whether that column is currently blank for all records or if it already has data in it to which we are adding a flag.

Also if you find yourself "not understanding what [I am] asking?" please say so. I'm also glad to clarify.

Again I must delay results as I'm also working on taxes.

That is fine, I understand you have other things you are currently working on.

Also, the corp_enh field is a blank field that I use for situations like these.  There is nothing currently in corp_enh so that can be updated as required.

Hope that helps,
Thanks again

Try this:

SELECT table
SCAN
	missing = .F.
	DECLARE oldwords[1], newwords[1]
	old = parse(orig_desc, @oldwords)
	new = parse(descript, @newwords)
	FOR EACH word IN oldwords
		IF ASCAN(newwords, word) = 0 && old word not found in new
			REPLACE corp_enh WITH "X"
			EXIT
		ENDIF 
	NEXT 
ENDSCAN 
 
PROCEDURE parse(data,  words)
	data = UPPER(ALLTRIM(data))
	w = 0 && word counter
  word = ''
  words = ''
  FOR i = 1 TO LEN(data) + 1
    ch  = SUBSTR(data, i, 1)
    IF BETWEEN(ASC(ch), 65, 90) OR BETWEEN(ASC(ch), 48, 57) && alphanumeric
    	word = word + ch
    ELSE && separator 
    	IF NOT EMPTY(word)
    	  w = w + 1
    	  DECLARE words[w]
    	  words[w] = word
   	  ENDIF 
  	  word = ''
    ENDIF
  NEXT 
ENDPROC 

Select allOpen in new window

you are a genuis!  Thank you for your help, it worked wonderfully!

Genius? I looked that up in the dictionary and to my surprise the definition you have in mind seems to be missing!

I think that my success in helping comes from many years of working with VFP and other languages and with text manipulation in general.

I also noted that my program creates old and new but does not give them meaningful values nor does it use them.
        old = parse(orig_desc, @oldwords)
        new = parse(descript, @newwords)
could be rewritten
        =parse(orig_desc, @oldwords)
        =parse(descript, @newwords)
However I then recall that a function can return an object which leads to defining an array class that can be reusable and simplifies the rest of the code (I also saw some other opportunites for improvement).

SELECT table 
SCAN && table 
  oldwords = parse(orig_desc)
  newwords = parse(descript)
  FOR EACH word IN oldwords.array
    IF newwords.ascan(word) = 0 && old word not found in new
      REPLACE corp_enh WITH "X"
      EXIT
    ENDIF 
  NEXT 
ENDSCAN 
 
PROCEDURE parse(data)
  words = CREATEOBJECT('array')
  data = UPPER(ALLTRIM(data))
  start = 1
  FOR end = 1 TO LEN(data) + 1
    ch = ASC(SUBSTR(data, end, 1))
    IF NOT BETWEEN(ch, 65, 90) AND NOT BETWEEN(ch, 48, 57) && separator
      IF end - start > 1
	      words.append(SUBSTR(data, start, end-start))
      ENDIF 
      start = end + 1
    ENDIF
  NEXT 
  RETURN words
ENDPROC 
  
DEFINE CLASS array as Custom
 
* emulate a 1 dimensional array as much as possible
* allow for zero length
* provide convenience methods such as append, ascan, alen and clear
* if this were Python we could do a lot more, and do it easier!
 
* Future Objectives: 
*  extend to > 1 dimensions
*  adel, ains (that also reshape), asort
*  shape, reshape
*  slice (ala Python) - return a new array object
 
  DECLARE array[1]
  length = 0
  
  PROCEDURE init()
    WITH this
      .clear()
    ENDWITH 
  ENDPROC 
  
  PROCEDURE append(item)
    WITH this
      .length = .length + 1
      DECLARE .array[.length]
      .array[.length] = item
    ENDWITH 
  ENDPROC 
  
  PROCEDURE clear()
    WITH this
      .length = 0
      DECLARE .array[1]
      .array[1] = ""
    ENDWITH 
  ENDPROC 
 
  PROCEDURE alen()
    WITH this
      RETURN .length
    ENDWITH 
  ENDPROC 
 
  PROCEDURE item(subscript)
    WITH this
      RETURN .array[subscript]
    ENDWITH 
  ENDPROC 
  
  PROCEDURE ascan(item)
    WITH this
      RETURN ASCAN(.array, item)
    ENDWITH 
  ENDPROC 
  
ENDDEFINE     

Select allOpen in new window