Error . . .Query to Long . . .

[Rickinrsm]

Anyone run into this?

It's a CHANGETO query that runs through thousands of records.

I have CHANGETO based on an ID numbers to reflects a specific unique names.

I entered all of the data into the query with no problems but now the query won't open up. "Query to Long"

I tried to create a script and used "paste from" to place all of the query data there but it doesn't seem to run.

Thanks . . . Rick

 

[TonyMcGuire]

Sounds like you need to run several sequential (one after another) queries.

That, or set up a separate criteria table.

criteria.db | currentinfo | changetoinfo |
            | _a          | _b           |

tableWithDataToChange.db | fieldtochange    |
                         | _a, changeto _b  |

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Rickinrsm]

Let me try and understand this Tony.

I can create a Master table of the offices that I need to normalize with the office ID being the Keyed field.

There has to be a scan in here someplace, correct?

There could be 50 occourences of the same office ID in the tableWithDataToChange.db

In addition, there are two separate fields with listing office ID number and selling office ID numbers followd by the office names that I need to normalize.

I'm not sure I understand this Tony.

Thanks . . . Rick

 

[TonyMcGuire]

I'm completely lost.

You have a field with data

abcd10

You need to change all occurances of abcd10 to something else.

Right?

Lets call that field OFFICEID

The criteria table would have an entry

| Currentdata | ChangetoData |
| abcd10 | abcd |



The main table:

| OFFICEID |
| abcd10 |




Now, just run the query I showed, and abcd10 will be changed to abcd. EVERY record will be changed.


What am I missing?







Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Rickinrsm]

Okay Tony. I try a few things out and see how it plays.

As always - THANKS!

Rick

 

[Rickinrsm]

Okay Tony - this is what I tried - but it failed.

q=

Query

ScmlsChangeToTable.db | OfficeID | NewOfficeName | ;This is the table with the normalized office names
| _ID | _Name | ;for each unique office ID number.
;(Office ID number never change, office names do.)

Scmls.db | ListID | ListName | ;This is the table to be normalized.
| _Name |


Scmls.db | SellID | SellName |
| _ID | _Name |


EndQuery

if not q.executeQbe()then
errorShow()
return
endif

 

[Rickinrsm]

I know I left the _ID out of Scmls.db | ListID | but after correcting it it still didn't work.

Help!

Thanks . . . Rick

 

[Rickinrsm]

I also get this error:

"Expecting consitent number of columns in all rows of table."

The table to be change has four fields to work with

ListID nunber ListName SellID number SellName in this order. There are other fields but these are the oines that I work with.

My table with the stored office ID numbers and Office Names is ScmlsChangeToTable.db
Two Fields; Office ID and NewOfficeName

Thats it.

Am I close tony?

Rick

ps. what is the code tag used here? [] or / ?

 

[TonyMcGuire]

In scmls.db image, you have two fields as headers, but only one below. You need to delete the ListID portion of the query. Or add a section for it on the second row.

Also, instead of doing everything at once, let's break this into 2 queries. Below is what we are left with (based on what you entered here)

qbe=Query

ScmlsC.db | OfficeID | NewOfficeName |
          | _ID      | _Name         |

Scmls.db  | ListID   | ListName      |
          | _ID      | _Name         |

endquery
qbe.executeqbe()


qbe=Query

ScmlsC.db | OfficeID | NewOfficeName |
          | _ID      | _Name         |

Scmls.db  | SellID   | SellName      |
          | _ID      | _Name         |

endquery
qbe.executeqbe()

The problem is, you aren't telling the query to DO anything.

qbe=Query

ScmlsC.db | OfficeID | NewOfficeName   |
          | _ID      | _Name           |

Scmls.db  | ListID   | ListName        |
          | _ID      | changeto _Name  |

endquery
qbe.executeqbe()

qbe=Query

ScmlsC.db | OfficeID | NewOfficeName   |
          | _ID      | _Name           |

Scmls.db  | SellID   | SellName        |
          | _ID      | changeto _Name  |

endquery
qbe.executeqbe()

Now, we've not only matched up 'officeID' and 'sellID' through the '_ID' tag, but we've told the system that when they match, change the 'ListName' or 'SellName' values to match the 'NewOfficeName' values.

Does this make sense?

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Rickinrsm]

Here is what I have. The code runs but nothing changes.

I have the SCMLS and SCMLSChangeToTable in the Scmls Direcctory and the form is in the working directory. Is this the problem?

method pushButton(var eventInfo Event)
var;----------------Declare Variables-------------
Scmls                table
ScmlsChangeToTable	table
qbe						query
endVar;-------------End Variable List-------------
addAlias("MYDIR","Standard","D:\\Pdoxwin7\\Scmls")
;----------------------------------------------------------------

qbe=Query

ScmlsChangeToTable.db | OfficeID | NewOfficeName   |
                      | _ID      | _Name           |

Scmls.db  | ListID   | ListName        |
          | _ID      | changeto _Name  |

endquery
	if not qbe.executeQbe()then
errorShow()
	return
	endif

qbe=Query

ScmlsChangeToTable.db | OfficeID | NewOfficeName   |
                      | _ID      | _Name           |

Scmls.db  | SellID   | SellName        |
          | _ID      | changeto _Name  |

endquery
	if not qbe.executeQbe()then
errorShow()
	return
	endif

endMethod

 

[Rickinrsm]

Okay Tony . . . It works perfectly on the Scmls.db that is in the working directory.

However, I need to have it work on the Scmls.db while it is in the Pdoxwin7\Scmls\ Directory.

I'll see if I can figure that out.

Sheesh . . . where would I be if you were'nt here Tony?

Thanks much.

Rick

 

[Rickinrsm]

Okay . . . I moved the form over to the Scmls directory where the two tables reside. It still doesn't work in the Scmls directory.

What am I missing here.

Thanks . . . Rick

 

[TonyMcGuire]

Create an alias to that directory. Maybe :scmls: ??

Add :scmls: to the front of each table in the query that is in that directory.

Do the same for the changeTo table; create a separate alias if the changeTo table isn't in the same location.

DO NOT depend on things being in the :WORK: directory. This causes what you are seeing - works one place but not universally.

This SHOULD allow the query to run from anywhere, basically.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Rickinrsm]

Tony - how would you like enumeration for all of your assistance to me?

I wish I had the knowledge that you have.

If you were to se my programs - they work - but I think they are really archaic. Ha! they probably could be fine tuned and run faster than what I've bastardized.

Thanks so much Tony.

Rick

 

[TonyMcGuire]

Tony - how would you like enumeration for all of your assistance to me?

Thanks so much Tony.

Account is paid.

Just glad I could help.

Further payment can be made, however...

If there is a question here you can help out with, chime in.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Rickinrsm]

Hi Again Tony. Sorry to bother you.

This morning after I finished adding all of the offices to the ScmlsChangeToTable.db and ran the code I got the following error;

Alias not currently opened.
Table: :Scmls:ScmlsChangeToTable.db

The error window list both oocurances of this call in the queries.

Any ideas?

Rick

 

[Rickinrsm]

Oh . . . I changed the Alias statement and it worked.

Will wonders never cease?

Thanks . . . Rick