comparing the fields in a database and storing it in a different table

[sun11]

Hi,

I have table called orders and table called nielsen.

so iam searching for the catalog_no which is in table nielsen and ide to match in orders table so that they proceed for checkout.

before i used the table directly like this


SELECT IDE
GO TOP

DO WHILE NOT EOF()
IF DIST <> 'XX'
SELECT whit %%%%&&&&( I want to replace this one by select command so that i can directly compare )***
!!!!***I tried like this but it gives an error saying indexnot found****!!!!
SELECT * FROM nielsen WHERE catalog_no like '"+ALLTRIM(m.catalog_no)+"'

SET ORDER TO (catalog_no)
IF SEEK(ALLTRIM(ide -> catalog_no))
temdist = ALLTRIM(UKDIST1)
SELECT sup
SET ORDER TO UKDIST1
IF SEEK(ALLTRIM(temdist))

SELECT ide
REPLACE sup WITH sup -> acc_ref
REPLACE dist WITH sup -> acc_ref
REPLACE PUBTAG WITH 'EP'
ELSE

REPLACE ide -> sup WITH 'QUERY'
REPLACE ide -> dist WITH whit -> UKDIST1

ENDIF
ELSE
ENDIF
ENDIF
SELECT ide
SKIP
ENDDO

Thanks,
sunil

 

[JRB-Bldr]

"SELECT * FROM nielsen WHERE catalog_no like '"+ALLTRIM(m.catalog_no)+"'"

Well, lets see....

First the error message index not found appears to be a 'red herring' in that it should not be occurring due to the running of a SQL Query unless the table Nielsen SHOULD have some index and it is missing. In that circumstance opening the table (not running the Query) would be throwing the error message. But, for now, let's ignore it.

Next, as written above, you have a syntax error in your WHERE clause:
LIKE Single Quote, Double Quote, Plus Sign
It is un-balanced. It should be:
LIKE Double Quote, Single Quote, Double Quote, Plus Sign
Maybe that's only a typo entered during your question posting.

I have no idea where you are getting m.Catalog_No, but I'll assume that you get its value OK.

Questions:
1. Is the value type for m.Catalog_No a Character?
2. Is the value type of Nielsen.Catalog_No character?
3. When correct, is Nielsen.Catalog_No supposed to be the SAME as m.Catalog_No?
3. Is Nielsen a VFP data table?
4. If so then why not use:
WHERE ALLTRIM(Nielsen.Catalog_No) == ALLTRIM(m.Catalog_No)
5. If not then why not use:
WHERE ALLTRIM(m.Catalog_No) $ ALLTRIM(Nielsen.Catalog_No)

Good Luck,
JRB-Bldr

 

[cbasoz]

IMHO there should be no quotes
Also IMHO alltrim() is an overkill and possibly would suppress benefiting from an existing index.

where nielsen.catalog_no == m.catalog_no

is much better and optimizable if an index exists (unless there is a chance the values could have spaces on left which should ring bells as warning - in other words AllTrim() here merely means LTrim() and unnecessary).

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

First the error message index not found appears to be a 'red herring' in that it should not be occurring due to the running of a SQL Query unless the table Nielsen SHOULD have some index and it is missing. In that circumstance opening the table (not running the Query) would be throwing the error message.

Maybe not a red herring, JRB-Bldr. If the table is supposed to have an index, but the index is missing, my guess is that the error would be generated, even though it's a SQL command.

The command will try to open the table behind the scenes. If the index is not found, you'd get the error.

Sun11, you should try opening the table using a normal USE command. If you get the same error message, then you need to fix the index.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

Mike - as to my 'red herring' comment...

Unless the command SELECT * FROM nielsen is, by itself, opening the table, the table should already be open from code prior to that point (not shown).

If the table were already open then the index not found error message should have been seen at the time of the USE Nielsen command and not upon execution of the SQL SELECT command.

However if the table Nielsen were not open previously and the SQL SELECT command was, itself, opening the table, then Yes the error message would be displayed at that time due to opening the table, not due to other SQL SELECT issues.

Regardless, going off of the sample code shown above, sun11 has a few other SQL SELECT syntax issues to clear up.

sun1l - Good Luck,
JRB-Bldr

 

[Mike Lewis]

JRB-Bldr,

Yes, I think we agree on those points.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business