Can't you just use a Convert statement in your SELECT and GROUP BY clause
for the Bit fields?
For example
SELECT sum(line.total) as OrderTotal, Convert(Char(1), order.Sent) as
Shipped, order.ShipDate, order.ordernum
from line, order
where line.order_id = order.order_id
AND order.sent = 1 AND order.customer_id = #customerid#
group by Convert(Char(1), order.Sent), order.ShipDate, order.ordernum
I've done this several times on SQL Server platforms and it works with no
problem.
Hatton Humphrey
> -----Original Message-----
> From: Bryan Love [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 04, 2001 1:36 PM
> To: CF-Talk
> Subject: RE: help with moving from access to sql server
>
>
> BIT is the correct type to use for a boolean value, but you must
> think about
> it first since BIT typed columns cannot be included in a GROUP BY clauses.
> TINYINT is the one to use for boolean values if you need to group by a
> column.
>
>
> Bryan Love ACP
> Internet Application Developer
> Telecommunication Systems Inc.
> [EMAIL PROTECTED]
>
>
>
> -----Original Message-----
> From: Declan Maher [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 04, 2001 7:41 AM
> To: CF-Talk
> Subject: help with moving from access to sql server
>
>
> Could anybody help me with the following error regarding datatypes that
> I am getting having just moved my database from Access to SQL Server 7.
> I am receiving the following error:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot group by a bit
> column.
> The error occurred while processing an element with a general identifier
> of (CFQUERY), occupying document position (21:1) to (21:53).
> I realise that this is caused by trying to group according to the bit
> datatype.
> However when I remove the grouping for this it gives an error saying
> that I need to group.
> This is the query I am using. Can anybody suggest a way of getting
> around this.
>
> <cfquery name="gethdrs" datasource="#Variables.DSN#">
> SELECT DISTINCT Programme_Hdr.Business_Unit,
> Programme_Hdr.Programme_Name, Programme_Hdr.Region,
> Programme_Hdr.Programme_ID, Programme_Details.Year,
> Programme_Details.Quarter, Programme_Details.Quarter_ID,
> Programme_Details.Locked, Programme_details.Prog_Status,
> Programme_Details.ILX, Programme_Details.Executive,
> Programme_Details.BSS, Programme_Details.Prog_result
> FROM Programme_Hdr, Programme_Details, Business_Units2
> WHERE Programme_Hdr.Programme_ID Programme_Details.Programme_ID
>
> <cfif form.Programme_Name NEQ "ALL">
> AND Programme_Name = '#Form.Programme_Name#'
> </cfif>
> <cfif form.year NEQ "ALL">
> AND Programme_Details.Year = '#Form.Year#'
> </cfif>
> <cfif form.quarter NEQ "ALL">
> AND Programme_Details.Quarter = '#Form.Quarter#'
> </cfif>
> <cfif form.Business_unit NEQ "ALL">
> AND Programme_Hdr.Business_Unit = '#form.Business_Unit#'
> </CFIF>
> <cfif listcontains(session.access,'5') AND NOT
> listcontains(session.access,'1') AND NOT
> listcontains(session.access,'4')>
> AND Programme_Details.Executive = 1
> OR (PROGRAMME_DETAILS.ILX=1 AND PROG_Status IN('CURRENT,COMPLETED'))
> </cfif>
> <cfif listcontains(session.access,'3') AND NOT
> listcontains(session.access,'1') AND NOT
> listcontains(session.access,'4')>
> AND (PROGRAMME_DETAILS.BSS=1 OR (Programme_Hdr.Business_unit
> '#session.b_unit#' AND Programme_Details.BSS = 0))
> </cfif>
> <cfif listcontains(session.access,'2') AND NOT
> listcontains(session.access,'1') AND NOT
> listcontains(session.access,'4')>
> AND (PROGRAMME_DETAILS.ILX=1 OR (Programme_Hdr.Business_unit
> '#session.b_unit#' AND Programme_Details.ILX = 0))
> </cfif>
> <cfif NOT listcontains(session.access, '1') AND NOT
> listcontains(session.access, '2') AND NOT listcontains(session.access,
> '5')>
> AND Business_Units2.bss_Name = '#session.bss#'
> AND Programme_Hdr.Business_Unit = Business_Units2.bu_name
> </cfif>
> <cfif listcontains(session.access, '4') AND NOT
> listcontains(session.access, '5')>
> AND Programme_Hdr.Business_Unit = '#session.b_unit#'
> </cfif>
> AND Programme_Details.archived = 0
> Group By Programme_Details.Quarter_ID, Programme_Hdr.Business_Unit,
> Programme_Hdr.Programme_Name, Programme_Hdr.Region,
> Programme_Hdr.Programme_ID, Programme_Details.Year,
> Programme_Details.Quarter, Programme_Details.Locked,
> Programme_Details.Prog_Status, Programme_Details.ILX,
> Programme_Details.BSS, Programme_Details.Executive,
> Programme_Details.Prog_result
> <cfif not parameterexists(url.orderby)>
> ORDER by Programme_Hdr.Business_Unit, Programme_Details.Quarter
> <Cfelse>
> <cfif parameterexists(url.Orderby) and url.orderby eq "BU">
> Order by Programme_Hdr.Business_Unit
> <CFIF parameterexists(url.order) and url.order
> eq "asc">ASC<cfelse>DESC</cfif>
> <CFELSEIF parameterexists(url.Orderby) and url.orderby eq "PN">
> Order by PROGRAMME_hdr.Programme_Name
> <CFIF parameterexists(url.order) and url.order
> eq "asc">ASC<cfelse>DESC</cfif>
> <CFELSEIF parameterexists(url.Orderby) and url.orderby eq "YR">
> Order by PROGRAMME_DETAILS.YEAR
> <CFIF parameterexists(url.order) and url.order
> eq "asc">ASC<cfelse>DESC</cfif>
> <CFELSEIF parameterexists(url.Orderby) and url.orderby eq "QR">
> Order by PROGRAMME_DETAILS.Quarter
> <CFIF parameterexists(url.order) and url.order
> eq "asc">ASC<cfelse>DESC</cfif>
> <CFELSEIF parameterexists(url.Orderby) and url.orderby eq "RN">
> Order by PROGRAMME_HDR.Region
> <CFIF parameterexists(url.order) and url.order
> eq "asc">ASC<cfelse>DESC</cfif>
>
> </cfif>
> </cfif>
> </cfquery>
>
> thanks,
> Regards,
>
>
> Declan Maher
> Web Applications Developer
> Head Office
> 133-137 Lisburn Road
> Belfast
> Northern Ireland
> BT9 7AG
> T: +44 (0) 28 9022 3224
> F: +44 (0) 28 9022 3223
> E: [EMAIL PROTECTED]
> W: biznet-solutions.com
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
