Join to single record table creates "Ghost" records

[MPeccorini]

Hi fellows,

I have a very strange situation. I have this query:

select 
  consAccount.skglaccount as id,
  count(*)
from 
  glaccount as consAccount,
  glaccount as account
where
  left(convert(account.glaccountnumber,SQL_CHAR),4) = 
    left(convert(consAccount.glaccountnumber,SQL_CHAR),4)
  and consAccount.postingAttribute = 1
group by
  consAccount.skglaccount

... and it returns 104 rows (which is correct). But if I add the glinfo table (which has just 1 record) like this:

select 
  consAccount.skglaccount as id,
  count(*)
from 
  glaccount as consAccount,
  glaccount as account,
  glinfo as info
where
  left(convert(account.glaccountnumber,SQL_CHAR),4) = 
    left(convert(consAccount.glaccountnumber,SQL_CHAR),4)
  and consAccount.postingAttribute = 1
  and info.skrecordno = 0
group by
  consAccount.skglaccount

... and note that I even filter the record (just to be sure). Then it returns 260 records.

This sounds like IMPOSIBLE, but belive me, it is happening. Does somebody have any idea why?

Thanks a lot for your help,

Mauricio Peccorini

 

[mirtheil]

First, what version of Pervasive are you using?
Second, what tool are you using to issue the statement (PCC/ODBCTest/Access/etc)? If it's not the PCC, what does PCC return?
Third, does your database pass a Consistency check?
Fourth, what happens if you remove the filter on the info table?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[MPeccorini]

First: Pervasive SQL 2000i
Second: PCC (SQL Data Manager)
Third: It fails with each and every table because paths are absolute instead of relative, but I've always thought this was due to the way the application is programmed. Isn't it? Is there any other check I should do? Should I correct this situation? How?
Fourth: The same result (and execution plans did change so there is no corruption in the skrecordno index)

Thanks a lot for your help,

 

[mirtheil]

Are there any other failures or are the abosolute paths the only failure?
What version of 2000i (SP3 or SP4)? Check the W3ODBCCI.DLL/W3ODBCEI.DLL for version.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[MPeccorini]

Here is what the consistency check reports:

Table Name = GLInfo

The table location is a full path instead of a relative path.
Location = 'C:\BWDATA\TAG020\GLInfo.mkd'
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Unable to open table: X$File.

... and here are the versions of pervasive and the ODBC interfaces:

Pervasive.SQL 2000i SP4
Pervasive ODBC Client Interface -> 7.94.251.002
Pervasive ODBC Engine Interface -> 7.94.251.002

Once again, thanks a lot for the help !

 

[mirtheil]

Do the X$FILE, X$INDEX, and X$FIELD system tables pass a consistency check? If not, can you post the failures?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[MPeccorini]

Table Name = X$Field

The table location is a full path instead of a relative path.
Location = 'C:\BWDATA\TAG020\field.ddf'
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Unable to open table: X$File.


Table Name = X$File

The table location is a full path instead of a relative path.
Location = 'C:\BWDATA\TAG020\file.ddf'
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Unable to open table: X$File.


Table Name = X$Index

The table location is a full path instead of a relative path.
Location = 'C:\BWDATA\TAG020\index.ddf'
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Unable to open table: X$File.

 

[mirtheil]

I've never seen system tables with paths in them. Are the DDFs located in "C:\BWDATA\TAG020\"?
Also, how were these DDFs created?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[MPeccorini]

Yes, they are located there. The application (Business Works Gold) has an "import" utility to create a whole database from a backup. That utility created those definition files.

Best regards,

Mauricio Peccorini

 

[mirtheil]

Have you contacted Business Works to find out why they are creating tables like that? They might have new DDFs that fix some of these problems.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com