Crosstab and NOT EXISTS

B

Bas Cost Budde

I created custom filtering for my user. In the process a NOT EXISTS
subquery may be created, which produces a set of IDs.

I join the original table to this set, and have a crosstab on this. Now,
Access complains that it cannot resolve the alias I need for the NOT
EXISTS. The message must be similar to

"cannot recognize buiten.ID as a valid fieldname or expression"

where 'buiten' is my alias. (It means outer, but that is a reserved word
in SQL)

Note that if another rule than fltcost17 is used, and this rule doesn't
contain the (NOT) EXISTS, all works fine!


This is most of my SQL code, maybe somebody can read it.

crosstab query, upper level:

TRANSFORM First(vlRelatie_s.waarde) AS EersteVanwaarde
SELECT vlRelatie_s.relatieID, vlRelatie_s.rtype, vlRelatie_s.naam
FROM vlRelatie_s INNER JOIN fltcost_AutGron ON vlRelatie_s.relatieID =
fltcost_AutGron.relatieID
GROUP BY vlRelatie_s.relatieID, vlRelatie_s.rtype, vlRelatie_s.naam
PIVOT [veldtitel] & [volgnummer];

right branch:
fltcost_AutGron (allows for multiple criteria extension hence the alias.
You figure the next alias name?)

SELECT DISTINCT L1.relatieID
FROM fltcost17 AS L1;

fltcost17 (one line of the rule set):

SELECT DISTINCT buiten.relatieID
FROM relatieveldwaarde AS buiten
WHERE (((Exists (SELECT relatieID FROM Relatieveldwaarde WHERE
relatieID=buiten.relatieID AND veldID=141))<>False));

(I have my feelings about this rewriting of otherwise correct syntax,
but is that the issue here?)

Relatieveldwaarde is a table containing relatieID, veldID, volgnummer,
waarde

left branch:
vlRelatie_s basically retrieves several column sources, defined in table
USysViewConfig, from Relatieveldwaarde

SELECT Relatie.relatieID, Relatietype.omschrijving AS rtype,
Relatie.naam, vRelatieveldwaarde.veldtitel,
vRelatieveldwaarde.volgnummer, vRelatieveldwaarde.waarde,
USysViewConfig.volgorde, USysViewConfig.breedte
FROM Relatietype INNER JOIN ((Relatie INNER JOIN vRelatieveldwaarde ON
Relatie.relatieID = vRelatieveldwaarde.relatieID) INNER JOIN
USysViewConfig ON vRelatieveldwaarde.veldtitel =
USysViewConfig.veldtitel) ON Relatietype.relatietypeID =
Relatie.relatietypeID
WHERE (((Relatie.status)<>'weg') AND
((USysViewConfig.operator)=getdatabaseuser()));

this one does what it should.
 
D

Duane Hookom

Crosstab queries are probably the least tolerant of fancy syntax. I have
often had to resort to horrible use of DLookup() or other domain aggregate
functions in Crosstabs.
 
C

Chris2

Bas Cost Budde said:
I created custom filtering for my user. In the process a NOT EXISTS
subquery may be created, which produces a set of IDs.

I join the original table to this set, and have a crosstab on this. Now,
Access complains that it cannot resolve the alias I need for the NOT
EXISTS. The message must be similar to

"cannot recognize buiten.ID as a valid fieldname or expression"
Click to expand...

I cannot locate "buiten.ID" in any part of the sample code below. Which
Query is issuing the error message?

where 'buiten' is my alias. (It means outer, but that is a reserved word
in SQL)

Note that if another rule than fltcost17 is used, and this rule doesn't
contain the (NOT) EXISTS, all works fine!


This is most of my SQL code, maybe somebody can read it.

crosstab query, upper level:

TRANSFORM First(vlRelatie_s.waarde) AS EersteVanwaarde
SELECT vlRelatie_s.relatieID, vlRelatie_s.rtype, vlRelatie_s.naam
FROM vlRelatie_s INNER JOIN fltcost_AutGron ON vlRelatie_s.relatieID =
fltcost_AutGron.relatieID
GROUP BY vlRelatie_s.relatieID, vlRelatie_s.rtype, vlRelatie_s.naam
PIVOT [veldtitel] & [volgnummer];

right branch:
fltcost_AutGron (allows for multiple criteria extension hence the alias.
You figure the next alias name?)

SELECT DISTINCT L1.relatieID
FROM fltcost17 AS L1;

fltcost17 (one line of the rule set):

SELECT DISTINCT buiten.relatieID
FROM relatieveldwaarde AS buiten
WHERE (((Exists (SELECT relatieID FROM Relatieveldwaarde WHERE
relatieID=buiten.relatieID AND veldID=141))<>False));

(I have my feelings about this rewriting of otherwise correct syntax,
but is that the issue here?)

Relatieveldwaarde is a table containing relatieID, veldID, volgnummer,
waarde

left branch:
vlRelatie_s basically retrieves several column sources, defined in table
USysViewConfig, from Relatieveldwaarde

SELECT Relatie.relatieID, Relatietype.omschrijving AS rtype,
Relatie.naam, vRelatieveldwaarde.veldtitel,
vRelatieveldwaarde.volgnummer, vRelatieveldwaarde.waarde,
USysViewConfig.volgorde, USysViewConfig.breedte
FROM Relatietype INNER JOIN ((Relatie INNER JOIN vRelatieveldwaarde ON
Relatie.relatieID = vRelatieveldwaarde.relatieID) INNER JOIN
USysViewConfig ON vRelatieveldwaarde.veldtitel =
USysViewConfig.veldtitel) ON Relatietype.relatietypeID =
Relatie.relatietypeID
WHERE (((Relatie.status)<>'weg') AND
((USysViewConfig.operator)=getdatabaseuser()));

this one does what it should.
Click to expand...
 
B

Bas Cost Budde

Chris2 said:
I cannot locate "buiten.ID" in any part of the sample code below. Which
Query is issuing the error message?
Click to expand...

Sloppy wording by me. The message states 'buiten.relatieID'.
The alias is located here:

The error message occurs at the top level query (the crosstab one)
 
B

Bas Cost Budde

Duane said:
Crosstab queries are probably the least tolerant of fancy syntax. I have
often had to resort to horrible use of DLookup() or other domain aggregate
functions in Crosstabs.
Click to expand...
Would that suggest not using the NOT EXISTS predicate? Hm, there are
probably other ways to get at my dataset, like the left join looking for
Nulls--the familiar Unmatched Query. I'll have a look into it.
 
C

Chris2

Bas Cost Budde said:
Sloppy wording by me. The message states 'buiten.relatieID'.
The alias is located here:
Click to expand...

The following has been realinged for readability. The () have been lined up
exactly.


SELECT DISTINCT
buiten.relatieID
FROM relatieveldwaarde AS buiten
WHERE (((Exists
(SELECT relatieID
FROM Relatieveldwaarde
WHERE relatieID = buiten.relatieID
AND veldID = 141
)
) <> False
)
);


First, the above query needs some modifications

SELECT DISTINCT
buiten.relatieID
FROM relatieveldwaarde AS buiten
WHERE (((Exists
(SELECT buiten2.relatieID
FROM Relatieveldwaarde AS buiten2
WHERE buiten2.relatieID = buiten.relatieID
AND veldID = 141
)
) <> False
)
);



I've given the SubQuery a table alias of buiten2, so that "relatieID" from
the outer and inner query can be distinguished.

Try modifying the fltcost17 this way, and then try to run the Crosstab,
and see what happens. Maybe resolving the exact table for relatieID in the
SubQuery will solve the problem in the Queries calling it. I wasn't able to
test this, though, and am not sure that it will work.
 
B

Bas Cost Budde

Thank you for your time!
The following has been realigned for readability. The () have been lined up
exactly.
Click to expand...

Good suggestion, I will do that next time.
WHERE (((Exists ....
) <> False
Click to expand...

If only Access understood how to write EXISTS! But I don't think that is
the real issue, again.
First, the above query needs some modifications ....
(SELECT buiten2.relatieID
FROM Relatieveldwaarde AS buiten2
WHERE buiten2.relatieID = buiten.relatieID
AND veldID = 141
)
Click to expand...
I've given the SubQuery a table alias of buiten2, so that "relatieID" from
the outer and inner query can be distinguished.
Click to expand...

I see that, but I doubt that should make any difference in principle.
Isn't field name resolution automatically closest in scope, if I may
express it that way?
Try modifying the fltcost17 this way, and then try to run the Crosstab,
and see what happens. Maybe resolving the exact table for relatieID in the
SubQuery will solve the problem in the Queries calling it. I wasn't able to
test this, though, and am not sure that it will work.
Click to expand...

Well, it doesn't. Since fltcost17 is aliased already in an intermediate
step (the one to fltcost_AutGron), this translation gets lost anyway. I
guess.
 
B

Bas Cost Budde

As a matter of fact, I should have posted the version with Exists()=false

because otherwise a simple SELECT is sufficient!
 
B

Bas Cost Budde

Bas said:
Would that suggest not using the NOT EXISTS predicate? Hm, there are
probably other ways to get at my dataset, like the left join looking for
Nulls--the familiar Unmatched Query. I'll have a look into it.
Click to expand...

This works. The crosstab query no longer complains.

For the record, the statements become

fltcost17:

SELECT DISTINCT relatieveldwaarde.relatieID
FROM relatieveldwaarde LEFT JOIN fltcost17_1
ON relatieveldwaarde.relatieID = fltcost17_1.relatieID
WHERE fltcost17_1.relatieID Is Null;

and fltcost17_1 (this effectively overcomes the subquery problem; my
guess is that that is what really causes the crosstab to stumble):

SELECT Relatieveldwaarde.relatieID
FROM Relatieveldwaarde
WHERE Relatieveldwaarde.veldID=141;
 
Top