I am using DBIsam V4.19.
What is wrong with my Query 1 as I do not get the correct result set.  Query
2 gives me the correct result set which I thought Query 1 would produce.

/* Query 1: */
select a.AcctName, b.companyDesc, a.mySE, a.MyId, b.acctSetDesc, b.AcctSet,
a.cbcacctno
from "alpha\AcctDB" as a left outer  join "beta\Mycompany" as b
on a.cbcacctno=b.coAcctNo
where a.checkok=true and b.GrpID='1234'
order by a.AcctName

/* I also tried:
select a.AcctName, b.companyDesc, a.mySE, a.MyId, b.acctSetDesc, b.AcctSet,
a.cbcacctno
from "alpha\AcctDB" as a left outer  join "beta\Mycompany" as b
on a.cbcacctno=b.coAcctNo and a.checkok=true and b.GrpID='1234'
order by a.AcctName */


/* SQL2:  this produced the correct result set i need */

select * into "memory\dummya" from "alpha\AcctDB" where checkok=true;
select * into "memory\dummyb" from "beta\Mycompany" where GrpId='1234';
select a.AcctName, b.companyDesc, a.mySE, a.MYid, b.acctSetDesc, b.AcctSet,
a.cbcacctno
from "memory\dummya" as a left outer  join "memory\dummyb" as b
on a.cbcacctno=b.coAcctNo
order by a.AcctName

Have you tried this with a current DBISAM version?

There have been number of fixes since 4.19, including for example this one
that might affect you  :
http://www.elevatesoft.com/incident?action=viewaddr&category=dbisam&release=4.20&type=f&incident=2011

Raul


"G Patena" <george@patena.net> wrote in message
news:8F2B1663-085A-43FA-8BC4-2D24A95189A1@news.elevatesoft.com...
> I am using DBIsam V4.19.
> What is wrong with my Query 1 as I do not get the correct result set.
> Query 2 gives me the correct result set which I thought Query 1 would
> produce.
>
> /* Query 1: */
> select a.AcctName, b.companyDesc, a.mySE, a.MyId, b.acctSetDesc,
> b.AcctSet, a.cbcacctno
> from "alpha\AcctDB" as a left outer  join "beta\Mycompany" as b
> on a.cbcacctno=b.coAcctNo
> where a.checkok=true and b.GrpID='1234'
> order by a.AcctName
>
> /* I also tried:
> select a.AcctName, b.companyDesc, a.mySE, a.MyId, b.acctSetDesc,
> b.AcctSet, a.cbcacctno
> from "alpha\AcctDB" as a left outer  join "beta\Mycompany" as b
> on a.cbcacctno=b.coAcctNo and a.checkok=true and b.GrpID='1234'
> order by a.AcctName */
>
>
> /* SQL2:  this produced the correct result set i need */
>
> select * into "memory\dummya" from "alpha\AcctDB" where checkok=true;
> select * into "memory\dummyb" from "beta\Mycompany" where GrpId='1234';
> select a.AcctName, b.companyDesc, a.mySE, a.MYid, b.acctSetDesc,
> b.AcctSet, a.cbcacctno
> from "memory\dummya" as a left outer  join "memory\dummyb" as b
> on a.cbcacctno=b.coAcctNo
> order by a.AcctName
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4815 (20100128) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4815 (20100128) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Raul,
We have tons of code with DBIsam 4.19, and latest builds as I understood
have changes that might affect the structure (memo/blobs?).  What we are
doing is go elevatedb on new projects accessing both dbIsam and edb during
the transition.  I will just deal with the known caveats for dbIsam 4.19 for
now.  Thanks.




"Raul" <raul@raul.ca> wrote in message
news:743A08A7-6B77-4B57-93E4-DC675AFB2355@news.elevatesoft.com...
> Have you tried this with a current DBISAM version?
>
> There have been number of fixes since 4.19, including for example this one
> that might affect you  :
> http://www.elevatesoft.com/incident?action=viewaddr&category=dbisam&release=4.20&type=f&incident=2011
>
> Raul

George,

<< We have tons of code with DBIsam 4.19, and latest builds as I understood
have changes that might affect the structure (memo/blobs?).  >>

Here are the release notes about this:

WARNING
There is a new design for the BLOB reading/writing that is not compatible
with 4.27 or earlier.  Therefore, it is extremely important that you do not
mix DBISAM 4.28 applications with DBISAM 4.27 or earlier applications.
Mixing the two versions can cause improper truncation of BLOB data, and that
includes using a 4.27 application on any data that has been updated by
DBISAM 4.28, even if it is not concurrently.  The issue is that 4.27 and
earlier versions recylcle BLOB blocks on a per-block basis, whereas 4.28 and
higher versions recycle BLOB blocks on a per-row basis.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thanks for the warning.  We will move to elevatedb.

George

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:2B7A3A00-44F7-4114-8E58-9451E597C10C@news.elevatesoft.com...
> George,
>
> << We have tons of code with DBIsam 4.19, and latest builds as I
> understood have changes that might affect the structure (memo/blobs?).  >>
>
> Here are the release notes about this:
>
> WARNING
> There is a new design for the BLOB reading/writing that is not compatible
> with 4.27 or earlier.  Therefore, it is extremely important that you do
> not mix DBISAM 4.28 applications with DBISAM 4.27 or earlier applications.
> Mixing the two versions can cause improper truncation of BLOB data, and
> that includes using a 4.27 application on any data that has been updated
> by DBISAM 4.28, even if it is not concurrently.  The issue is that 4.27
> and earlier versions recylcle BLOB blocks on a per-block basis, whereas
> 4.28 and higher versions recycle BLOB blocks on a per-row basis.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>