Skip to main content
E
erikmanTx
Participant
May 23, 2009
Answered

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

  • May 23, 2009
  • 2 replies
  • 899 views

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?

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    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

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 24, 2009

    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">

    E
    erikmanTxAuthor
    Participant
    May 25, 2009

    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.

    D
    Dan_BracukCorrect answer
    Inspiring
    May 24, 2009

    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

    E
    erikmanTxAuthor
    Participant
    May 25, 2009

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices