×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Join to single record table creates "Ghost" records

Join to single record table creates "Ghost" records

Join to single record table creates "Ghost" records

(OP)
Hi fellows,

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

CODE

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:

CODE

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

RE: Join to single record table creates "Ghost" records

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

RE: Join to single record table creates "Ghost" records

(OP)
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,

RE: Join to single record table creates "Ghost" records

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

RE: Join to single record table creates "Ghost" records

(OP)
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 !

RE: Join to single record table creates "Ghost" records

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

RE: Join to single record table creates "Ghost" records

(OP)
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.

RE: Join to single record table creates "Ghost" records

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

RE: Join to single record table creates "Ghost" records

(OP)
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

RE: Join to single record table creates "Ghost" records

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close