Query Compare revisited

  • Thread starter Thread starter Rover
  • Start date Start date
R

Rover

Maybe I misstated the real problem. Here is a further definition of the
problem:

When I run Query A (see below) it:

1) It gets equal compare on codes "E1CA" and "E1cA" whereas Query B
doesn't. I have verified that tbl_detail_Bene only contains "E1CA".
Query A is linking 2 tables while Query B has literals in the criteria
filelds. Why is Query B case sensitive and Query A not?

2) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
I need to resolve both issues but item 1 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc
FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =
ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
 
Hi Rover,

Just some thoughts w/o
reading previous posts....

"Where are these tables?"

The Jet engine is case-insensitive,
so I can only wonder if "ppbenf"
is a linked Paradox table (or in some
other non-Access db which IS case
sensitive).

Or...what types of fields are these.
Can we assume the text fields in both tables are
type TEXT (and not BINARY/Unicode)?

HOWTO: Perform a Case-Sensitive JOIN Through Microsoft Jet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q244693

Probably not much help, but had to ask.

Gary Walter
 
I think you've found it... This table is really a Cobol table and is
accessed through Relitivity using an ODBC connection. That, I guess
would explain it. Thanks.
Jim

Gary said:
Hi Rover,

Just some thoughts w/o
reading previous posts....

"Where are these tables?"

The Jet engine is case-insensitive,
so I can only wonder if "ppbenf"
is a linked Paradox table (or in some
other non-Access db which IS case
sensitive).

Or...what types of fields are these.
Can we assume the text fields in both tables are
type TEXT (and not BINARY/Unicode)?

HOWTO: Perform a Case-Sensitive JOIN Through Microsoft Jet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q244693

Probably not much help, but had to ask.

Gary Walter

Rover said:
Maybe I misstated the real problem. Here is a further definition of the
problem:

When I run Query A (see below) it:

1) It gets equal compare on codes "E1CA" and "E1cA" whereas Query B
doesn't. I have verified that tbl_detail_Bene only contains "E1CA".
Query A is linking 2 tables while Query B has literals in the criteria
filelds. Why is Query B case sensitive and Query A not?

2) runs forever (hours) and table tbl_detail_Bene only has 2000 records.
Its like the query thinks its running against a non keyed table and it
is not.
I need to resolve both issues but item 1 is more important

==========================
Query A gets the benefit code description (ppbenf.ppdesc_benf) from
ppbenf table. The source table (tbl_detail_Bene) fields INNER JOIN to
all ppbenf table's key fields.

Table tbl_detail_Bene is not keyed. Table ppbenf is.
===============================

Query A:

SELECT tbl_detail_Bene.ppbenB, tbl_detail_Bene.chplan,
tbl_detail_Bene.chplrv, tbl_detail_Bene.[CLL-BENEFIT-CD],
ppbenf.ppcode_benf, tbl_detail_Bene.cdbnsf, ppbenf.ppdesc_benf INTO
tbl_Detail_bene_desc
FROM tbl_detail_Bene INNER JOIN ppbenf ON (tbl_detail_Bene.cdbnsf =
ppbenf.ppsufx_benf) AND (tbl_detail_Bene.[CLL-BENEFIT-CD] =
ppbenf.ppcode_benf) AND (tbl_detail_Bene.chplrv = ppbenf.pprevl_benf)
AND (tbl_detail_Bene.chplan = ppbenf.ppplno_benf) AND
(tbl_detail_Bene.ppbenB = ppbenf.ppcnst_benf);

==================================
Query B:

SELECT ppbenf.ppcnst_benf, ppbenf.ppplno_benf, ppbenf.pprevl_benf,
ppbenf.ppcode_benf, ppbenf.ppsufx_benf, ppbenf.ppdesc_benf
FROM ppbenf
WHERE (((ppbenf.ppcnst_benf)="b") AND ((ppbenf.ppplno_benf)="97181A")
AND ((ppbenf.pprevl_benf)="02") AND ((ppbenf.ppcode_benf)="E1CA") AND
((ppbenf.ppsufx_benf)="C"));
Click to expand...
Click to expand...
 
Back
Top