Subject | RelationCount and GROUP BY clause |
---|---|
Author | Herbert Senner |
Post date | 2003-03-23T17:42:05Z |
I had a query like the following:
SELECT A.NR,
SUM(A.COUNT) AS SUM_OF_COUNT,
SUM(A.COUNT * B.FACTOR) AS SUM_OF_MULT
FROM Table1 A
JOIN Table2 B ON A.KEYFLD=A.KEYFLD
GROUP BY A.NR;
In order to analyze this query I needed the RelationNames.
But asking for the IB_Statement.Fields.RelationCount I got 0,
where I expected 2 (Table1, Table2).
The IB_Query.Fields.Columns[0].FullFieldName-Property showed a
value of "NR" where I expected the full qualifier "Table1.NR".
Setting up the query with
SELECT A.NR,
A.KEYFLD,
B.KEYFLD,
SUM(
...
GROUP BY A.NR, A.KEYFLD, B.KEYFLD
the Fields.Columns[0-2].FullFieldName were 'NR', 'KEYFLD' , 'KEYFLD',
I expected 'Table1.NR', 'Table1.KEYFLD', 'Table2.KEYFLD',
RelationCount again was 0.
At last I used a query without any aggregates but with the GROUP BY clause.
Again I had no chance to get the RelationNames with the above mentioned
properties, without GROUP BY I got the expected results.
So it seems to be the GROUP BY-clause which prevents to get the informations
about the relations.
Is it a bug?
Herbert Senner
SELECT A.NR,
SUM(A.COUNT) AS SUM_OF_COUNT,
SUM(A.COUNT * B.FACTOR) AS SUM_OF_MULT
FROM Table1 A
JOIN Table2 B ON A.KEYFLD=A.KEYFLD
GROUP BY A.NR;
In order to analyze this query I needed the RelationNames.
But asking for the IB_Statement.Fields.RelationCount I got 0,
where I expected 2 (Table1, Table2).
The IB_Query.Fields.Columns[0].FullFieldName-Property showed a
value of "NR" where I expected the full qualifier "Table1.NR".
Setting up the query with
SELECT A.NR,
A.KEYFLD,
B.KEYFLD,
SUM(
...
GROUP BY A.NR, A.KEYFLD, B.KEYFLD
the Fields.Columns[0-2].FullFieldName were 'NR', 'KEYFLD' , 'KEYFLD',
I expected 'Table1.NR', 'Table1.KEYFLD', 'Table2.KEYFLD',
RelationCount again was 0.
At last I used a query without any aggregates but with the GROUP BY clause.
Again I had no chance to get the RelationNames with the above mentioned
properties, without GROUP BY I got the expected results.
So it seems to be the GROUP BY-clause which prevents to get the informations
about the relations.
Is it a bug?
Herbert Senner