I need a column separator in the table header

[Paperpeople]

I have no knowledge of SQL but I've been working with Paradox for 12 years. I always just use queries. I can never get a query to "sort" the data or change the structure of the data so it "sorts itself". Whenever I try, it works once and then I save the query and try to open it again and I get the dreaded "expecting a column separator in the table header" message. I can never again open the query. I don't know how to put a column separator in the table header. I can run the query and sort the table OK but if the table is in a form I've got to jump through hoops to do this. How do I get the query to sort the table?

 

[TonyMcGuire]

I can't make heads or tails of what you have been doing, and what you can't do now.

Do you have a query set up to run from a form?

What about it doesn't work?

Or is the 'query' in the form's datamodel?

Help us to visualize your situation, please.

Tony McGuire

http://www.wjcitycouncil.com

"It's not about having enough time; we have the rest of our lives. It's about priorities.

 

[Paperpeople]

I'm running Paradox 9 on Windows xp

I'm a multi-line sales rep and I've developed a program to order all my lines and items through Paradox. I have a very detailed program at this point with many forms, reports , etc.

In looking at the problem further I've discovered it only happens with queries that gather information from two directories. The Forms are irrelevant. I have three tables in the Working directory and everything else in a directory called Ordering (which is set as my "working" directory). I did this a long time ago and have built my entire program from this.

If I design a query that gathers information across two directories and go into table properties to have it sort the result on any field other than the default it will work until I save and close the query. When I go to open the query again I get a window that says "expecting a column separator in the table header". I close the window, nothing happens and if I go to open the query again the same thing happens. I can never again open the query.

If I change the query and drop off the table(s)in the other directory it works fine. Table properties can be setup to sort the result, it closes and opens without a problem.

Please don't tell me I need to move the tables into one directory, I'd have to redo all my queries.

 

[TonyMcGuire]

What is the path to RIV:? (Full Path)

You usually get that when the resolved path to PRIV plus the first sort field name length together are longer than about 64 characters.

Cheapest way to test is to change the location of RIV: and make the path shorter.

Tony McGuire

http://www.wjcitycouncil.com

"It's not about having enough time; we have the rest of our lives. It's about priorities.

 

[Paperpeople]

I'm pretty sure the path is:
C:\Program Files\Corel\Shared\Private

unless there's another PRIV that is different than Private

When I go to open the answer table in the PRIV alias this is the path I get.
The sort name is either "sort, or "company".
Either way they don't come close to 64 characters

 

[Paperpeople]

I've opened the queries in the SQL editor
The first two work without problems
When I change it to sort by "Company" I have the problem

SELECT DISTINCT D1."Stock Number", D1.Description, D1."Sort"
FROM "Order Criteria.DB" D, "Order Archive.DB" D1
WHERE
(D1."Account Code" = D."Account Code")
ORDER BY D1."Sort", D1."Stock Number", D1.Description

SELECT DISTINCT D1."Stock Number", D1.Description, D1."Sort", D2.Company
FROM "Order Criteria.DB" D, "Order Archive.DB" D1, "C:\Program Files\Corel\WordPerfect Office 2000\Paradox\Working\Mfrs.DB" D2
WHERE
(D1."Account Code" = D."Account Code")
AND (D2.Logcode = D1.Mfr)
ORDER BY D1."Stock Number", D1.Description, D1."Sort", D2.Company

This one will get the column separator problem:

SELECT DISTINCT D1."Stock Number", D1.Description, D1."Sort", D2.Company
FROM "Order Criteria.DB" D, "Order Archive.DB" D1, "C:\Program Files\Corel\WordPerfect Office 2000\Paradox\Working\Mfrs.DB" D2
WHERE
(D1."Account Code" = D."Account Code")
AND (D2.Logcode = D1.Mfr)
ORDER BY D2.Company, D1."Stock Number", D1.Description, D1."Sort"

 

[TonyMcGuire]

Since C:\Program Files\Corel\WordPerfect Office 2000\Paradox\Working\Mfrs.DB is 71 characters, I'm going to guess that this is part of the problem.

Create a shorter path and move (copy) the mfrs.db file there; then use the shorter pathed table in the query.

I'm guessing that this is a different twist on the 64 character to riv: issue.

Tony McGuire

http://www.wjcitycouncil.com

"It's not about having enough time; we have the rest of our lives. It's about priorities.

 

[Paperpeople]

Well that solved the problem of the column separator but now all my queries using the Mfrs table point to the long file name path. Is there some way to make them use the Mfrs file I moved to the shorter path without having to rewrite them all?

 

[TonyMcGuire]

Just edit them with a text editor, and change the path.

Better yet, create an alias and point it to the shorter path (the error is based on the path things resolve to, so you MUST use that shorter path).

Then, when you edit the path, use the alias instead. It makes it so your queries are portable with a simple change of the path for the alias instead of all queries/forms/reports/etc that might use that path.

Further, it makes it so you don't have to depend on Paradox being set to use a particular :WORK: directory.

DISCLAIMER: this may be a bit different to set up (aliases) with SQL. And won't work with a SQL server table just Paradox tables. The SQL server syntax must use a database variable, but can work very similarly.


Tony McGuire

http://www.wjcitycouncil.com

"It's not about having enough time; we have the rest of our lives. It's about priorities.

 

[Paperpeople]

Ok. I think I'm nearly done with this problem. However years ago I named some of my fields with the symbol "#", like Code# and Account#. I have been having a problem when I translate the queries I have to SQL with the SQL editor. It says there is an error in line "XX" with a keyword and shows the # symbol.
I suspect this symbol is used by SQL and I can't use it for field names.
a) Is this the case?
b) If yes - I've been reluctant to change any field names. In the past if I did this it screwed up all my queries and they wouldn't open because it didn't recognize the field. I believe this has been corrected. Before I change my field names, do you know if this will be a problem in Paradox 9

 

[TonyMcGuire]

It isn't about the version of Paradox you use but the SQL language.

It is **highly** recommended that you only use a-z and 0-9 in field names - and this is exactly one of the reasons.


Tony McGuire

http://www.wjcitycouncil.com

"It's not about having enough time; we have the rest of our lives. It's about priorities.