Can someone help in figuring out this error!

[AJBJ]

This is my query:

<cfquery name="q" datasource="#APPLICATION.DB#" USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#">
        select    groupidfk AS group
        from    #variables.tableprefix#permissions
        where    rightidfk = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.right#" maxlength="35">
        and        resourceidfk = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.resource#" maxlength="35">
</cfquery>

This is the error I get: Incorrect syntax near the keyword group

If I take the alias out and not rename it group then the query works just fine.  But I need that in there for a bunch of other code to work properly.

Ideas?

Anthony

[Adam Cameron.]

You might want to check the reserved words for your DB implementation.  However I couldn't think of any SQL DB which would not have GROUP as a reserved word (eg: because of the GROUP BY statement).

If you need that column to be called "group", then investigate how your DB escapes keywords. It could be something like square brackets, or back-ticks or something like that.

--

Adam

[Joshua_Cyr · Accepted solution]

In at least some DB's you can get around the reserved word by putting quotes around it

select xyzcolumn as 'group'

[AJBJ]

Thanks, putting the single quotes got the problem working.