Sub queries, Cfqueryparam, and "Invalid Column Name"

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?
erikmanTXAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JuniorMemberCommented:

Try:

SELECT   department_staff_tbl.*,
               (   SELECT   max(bookmark_id) AS bookmark_id1
                   FROM       bookmarked_items_tbl
                   WHERE     bookmarked_items_tbl.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">


AND


SELECT   department_staff_tbl.department_id ,
               (   SELECT   max(bookmark_id) AS bookmark_id1
                   FROM       bookmarked_items_tbl
                   WHERE     bookmarked_items_tbl.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 worked, compare the syntax
0
erikmanTXAuthor Commented:
Nope.  No change.  Thanks though.
0
Andrew MaurerCommented:
Do you have ambiguous IDs in your select  and where? Can you try prefixing with the table name?
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

gdemariaCommented:
Look at the values of the three arguments

 arguments.deptID
 arguments.posID
 arguments.staffID

what are the values when the error occurs?  

I am thinking that the values are not matching the cfparam type requirement
0
cfdvlprCommented:
Turn debugging on and look at the SQL Queries output.  Notice anything strange about the SQL query?
0
SidFishesCommented:
guesses here

try fully referencing your fields or aliasing

FROM     department_staff_tbl
WHERE    department_staff_tbl.department_id = <cfqueryparam value="#arguments.deptid#" cfsqltype="cf_sql_integer">

FROM     department_staff_tbl DS
WHERE    DS.department_id = <cfqueryparam value="#arguments.deptid#" cfsqltype="cf_sql_integer">

don't use the * selector (it's never really a good idea anyways as it usually creates more db work than necessary)
0
erikmanTXAuthor Commented:
Thanks all.  The suggestions have all been excellent, but none of them have resolved the issue.  It seems to be something about the structure of multiple WHERE clauses (one in the subquery and another in the main query) and the use of the cfqueryparam, almost like cfquery doesn't understand the structure when parsing in the parameters, even though it is a perfectly valid SQL Server query.

A user on the Adobe board suggested doing a join instead of the subquery and this works, allowing me to get the data I need while still protecting the query with cfqueryparams.  Still I would really like to figure out if there is a way to use subqueries with multiple wheres and the cfqueryparam in another of my queries, so I'm going to leave this open for another week.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.