"Invalid Column" on multiple where clauses with subqueries and cfqueryparam

[erikmanTx]

I'm seeing a behavior in the coldfusion cfquery that I'd like to find an exmplanation for .  I've got a query that does a subquery in the select portion and if I have multiple where lines, I get an "invalid column name" message for my second where clause, but only when I'm using cfqueryparam

For example on the following I get "Invalid column name 'position_id'"

SELECT   department_staff_tbl.*,
         (   SELECT   max(bookmark_id)
             FROM     bookmarked_items_tbl
             WHERE    item_id = department_staff_tbl.staff_id

         ) AS bookmark_id
FROM     department_staff_tbl
WHERE    department_id = <cfqueryparam value="#arguments.deptid#"  cfsqltype="cf_sql_integer">
AND     position_id   = <cfqueryparam value="#arguments.posid#"   cfsqltype="cf_sql_integer">
AND     staff_id      = <cfqueryparam value="#arguments.staffid#" cfsqltype="cf_sql_integer">

If I change the order of my where clause so staff_id is first, then it tells me "department_id" is an invalid column.

If I only have one where clause, it works.  (i.e. WHERE position_id = <cfqueryparam value="#arguments.posid#" cfsqltype="cf_sql_integer">).

If I remove the where clause from my subquery (WHERE     item_id = department_staff_tbl.staff_id) it works.

It also works if I remove the cfqueryparam from my where clause so that my query looks like this:

SELECT   department_staff_tbl.*,
         (   SELECT   max(bookmark_id)
             FROM     bookmarked_items_tbl
             WHERE    item_id = department_staff_tbl.staff_id

         ) AS bookmark_id
FROM     department_staff_tbl
WHERE    department_id = #arguments.deptid#
AND     position_id   = #arguments.posid#
AND     staff_id      = #arguments.staffid#

Any thoughts?

[Dan_Bracuk · Accepted solution]

A more conventional way of getting your data is without the subquery.  Something like this

select some fields, max(bookmark_id) bookmarkid

from bookmarked_items__tbl  join department_staff_tbl on staff_id = bookmark_id

where etc

group by some fields

[erikmanTx]

Dan,

Thanks for posting this.  Setting up my query this way does allow me to pass the variable data using cfqueryparam.  The subquery would have been much simpler (and more readable down the road), but I think the use of cfqueryparam in this instance supercedes those other considerations.

I'm going to give you the correct answer, but if anyone knows why coldfusion behaves this way with an otherwise valid sql server query, I'd be very interested in learning.

[BKBK]

I see two tables. So can the server. So, use qualified column-names.

SELECT   department_staff_tbl.*,
         (   SELECT   max(bookmarked_items_tbl.bookmark_id)
             FROM     bookmarked_items_tbl
             WHERE    bookmarked_items_tbl.item_id = department_staff_tbl.staff_id

         ) AS bookmark_id
FROM     department_staff_tbl
WHERE    department_staff_tbl.department_id = <cfqueryparam value="#arguments.deptid#"  cfsqltype="cf_sql_integer">
AND      department_staff_tbl.position_id   = <cfqueryparam value="#arguments.posid#"   cfsqltype="cf_sql_integer">
AND      department_staff_tbl.staff_id      = <cfqueryparam value="#arguments.staffid#" cfsqltype="cf_sql_integer">

[erikmanTx]

It's really odd behavior.  Even with the explicit naming I still get "invalid column name", but only if I am using the cfqueryparam to pass the data.