Cannot Group by a Bit Column - Upsize Problem 1

[redbase]

I have upsized the tables of a database whilst keeping the forms within an mdb file. The intention is to distribute the mdb file to many user's local drives to reduce network traffic.

Problem I have is:

I cannot run certain queries any more because they have the group by and count functions in them. I get the error "Cannot group by a bit column (#252)".

What has happened? I'm new to sql so any help would be greatfully received.

Many thanks (in advance)

R

 

[TerryStover]

The functionality on bit data types is really limited - can't do joins or group by's, you can only use in the where or bitwise operators.
Unless you have alot of bit columns the easiest thing to do is change them to tinyint's. This will take up 1 byte per row instead of 1 bit per row (the first bit column takes 1 full byte, the next 7 are free). I have found the value gained in compression wasn't worth the pain in using the columns.
If it's only one or two columns you could move the bit to the where clause. Do a
"select ..... where bitcolumn1 = 1 group by ...
union all
select ..... where bitcolumn1 = 0 group by ..."
Finally, you could cast the bit at tinyint when you use it in the group by. That wouldn't be good for performance.

BTW, if the table is of substantial size, create a new empty table with the modified data types, insert from the original table, then drop the old table rename the new one (sort of a pain if you have RI constraints).

 

[tlbroadbent]

You must be using SQL 7 or earlier or the database is set to SQL 7 compatibility mode. Before SQL 2000, grouping by a bit column was not aloowed.

Here are some ways you group by the BIT column.

1) Convert the column to type INTEGER.

2) Convert the column to INTEGER when selecting.
[tt]
Select
col1, col2,
Bitcol=convert(int, bitcol),
max(col3) As Max3
From Table
Group By col1, col2, convert(int, bitcol)[/tt]
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[redbase]

Thank you both.

I have converted column types and columns to INTEGER to use group by. This works fine.

Terry - I have just had a look at your site:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

This is excellent - it covers a lot of the information I need to know about. I am making the transition from access jet to sql and I'm sure this will help me on my way.

Thanks

R

 

[tlbroadbent]

There are a many web sites that are better than mine. I haven't had much time to try to update, lately. When you are moving from Access to SQL I recommend the following link.

http://msdn.microsoft.com/library/en-us/dnacc97/html/acsqlres.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm