Group by in a view can this be used ?

In a complex view the group by gives strange (wrong) results.
(Adding a group by and having clause generates more rows instead of less).
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
from view_A
Results in : 720 rows
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F, count(*)
from view_A
group by A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
having count(*) > 1
Results in : 33678 rows (a lot of them containing a 1 in the count(*)
column)
(There should be only 57 rows)
If the view is put into a table : select * into table_A from view_A
First result : 720
Second result : 57
Is this a (known) bug,
(Removing the count(*) from the first query results in a :
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.
)
The view uses union to get the results form 3 queries, which each have
several tables. There are no correlated subqueries.
ben brugmanPlease can you post some code to reproduce the problem: the DDL for the base
tables and the view (just the key columns and the columns involved in the
query will do) plus a small sample of data (post as INSERT statements).
--
David Portas
--
Please reply only to the newsgroup
--|||I could generate a sample, but I would have
to anominise all data and meta data (table, view and column names).
There is a number of tables involved and I would have
to create suetable data.
(Sorry my organisation does not allow me to do this otherwise).
But then it does take such a form that I do not expect anybody to
look at the problem. And it would take a considerable amount of time
to prepare this.
At the end of this message I have done this only for the views and the
offending queries.
(Just as an example to show that this is not very user friendly).
Thanks for your attention
ben brugman
LOOK AT THE EXAMPLE AT YOUR OWN PERIL.
/* View for a selection. */
CREATE VIEW dbo.View_S
AS
SELECT T157TABLE.F637FIELD,
T157TABLE.F687FIELD AS F346FIELD,
T157TABLE.F638FIELD,
'SE' AS F347FIELD,
T116TABLE.F280FIELD AS F345FIELD,
T104TABLE.F703FIELD AS F238FIELD,
T157TABLE.F652FIELD,
T157TABLE.F744FIELD,
T157TABLE.F745FIELD,
T116TABLE.F277FIELD AS F246FIELD,
T116TABLE.F278FIELD AS F342FIELD,
T157TABLE.F324FIELD, T157TABLE.F309FIELD,
T157TABLE.F311FIELD,
T157TABLE.F588FIELD,
T157TABLE.F590FIELD,
T157TABLE.F747FIELD
FROM T157TABLE INNER JOIN
T116TABLE ON
T116TABLE.F637FIELD = T157TABLE.F637FIELD AND
T116TABLE.F687FIELD = T157TABLE.F687FIELD
LEFT OUTER JOIN
T104TABLE ON
T104TABLE.F637FIELD = T157TABLE.F637FIELD AND
T104TABLE.F687FIELD = T157TABLE.F687FIELD
AND T104TABLE.F230FIELD = 'SIS'
/* The main View */
CREATE VIEW dbo.View_A
AS
SELECT T122TABLE.F637FIELD,
T122TABLE.F344FIELD AS F346FIELD,
T122TABLE.F638FIELD,
'CT' AS F347FIELD,
T122TABLE.F262FIELD AS F345FIELD,
T122TABLE.F238FIELD,
T122TABLE.F246FIELD, T122TABLE.F342FIELD,
T122TABLE.F324FIELD,
T122TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T122TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T122TABLE.F747FIELD, NULL AS F652FIELD, NULL
AS F744FIELD, NULL
AS F745FIELD
FROM T122TABLE, T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T122TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T122TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT T123TABLE.F637FIELD,
T123TABLE.F348FIELD AS F346FIELD,
T123TABLE.F638FIELD,
'TT' AS F347FIELD,
T123TABLE.F737FIELD AS F345FIELD,
T123TABLE.F238FIELD,
T123TABLE.F246FIELD,
T123TABLE.F342FIELD,
T123TABLE.F324FIELD,
T123TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T123TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T123TABLE.F747FIELD, NULL
AS F652FIELD, NULL AS F744FIELD, NULL
AS F745FIELD
FROM T123TABLE,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T123TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T123TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT View_S.F637FIELD,
View_S.F346FIELD,
View_S.F638FIELD,
View_S.F347FIELD,
View_S.F345FIELD,
View_S.F238FIELD,
View_S.F246FIELD,
View_S.F342FIELD,
View_S.F324FIELD,
View_S.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
View_S.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
View_S.F747FIELD,
View_S.F652FIELD,
View_S.F744FIELD,
View_S.F745FIELD
FROM View_S,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE View_S.F311FIELD = IHCP_creator.F702FIELD
AND
View_S.F590FIELD = IHCP_mutator.F702FIELD
/* The query which goes 'wrong' */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD, count(*) from View_A
group by F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD
having count(*) > 1
/* The simple Query */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD from View_A