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
