Error incorrect select list on query?

Question

I have three queries:

<cfquery datasource="Intranet" name="getNotApproved">
  select submitterdept, COUNT(*) AS 'not_approved_manager'
  from [Intranet].[dbo].[CSEReduxResponses]
   where status =0 and approveddate is null
   group by submitterdept
</cfquery>

<cfquery datasource="Intranet" name="GetDepartments">
        SELECT *
        FROM CSEReduxDepts

</cfquery>
<cfquery dbtype="query" name="final_approved">
    select dep.csedept_id,DEP.csedept_name, COUNT(*) AS 'not_approved_manager'
from GetDepartments dep
join getNotApproved cs on cs.submitterdept = dep.csedept_id
where approveddate is null
group by dep.csedept_ID, dep.csedept_name
</cfquery>

When I run them on a cfm file it gives me a error:

Query Of Queries syntax error.
Encountered "\'not_approved_manager\'. Incorrect Select List,

which is on line <cfquery dbtype="query" name="final_approved"> I know these queries work because I have run them on my microsoft sql server 2008, they show me the correct output , all three running. So I'm trying to figure out why it doesnt work on coldfusion.

What happens if you remove the quotes from your names in your Select list? — Matt Busche, Jun 04 '14 at 21:24
i get a different error: Query Of Queries syntax error. Encountered "dep. — user3591637, Jun 04 '14 at 21:25
@math reason is because i do use getdepartments somewhere else too — user3591637, Jun 04 '14 at 21:26
Run your combined query and run your other query separately. Don't use query of query because it doesn't save you anything — Matt Busche, Jun 04 '14 at 21:32
Why are you using a query of queries for this? You can combine all three of those `cfquery` calls into one by using `JOIN` in the actual DBMS. QoQ is nice, but it is not best option here. Let the DB handle retrieving data, that is what it is designed for. — Scott Stroz, Jun 04 '14 at 23:51
@scott being new to this still learning, i got it to work this way, i will try to do it into one, so to answer your question i didnt know what other way to do it — user3591637, Jun 05 '14 at 00:22
It is all good. You should be able to use `JOIN` in the new query you write and I bet it is more performant. — Scott Stroz, Jun 05 '14 at 12:08

score 0 · Accepted Answer · answered Jun 04 '14 at 21:43

0

Query of queries does not support the keyword "join". So, in addition to the other things going on, this:

from GetDepartments dep
join getNotApproved cs on cs.submitterdept = dep.csedept_id

has to become something like this:

from GetDepartments dep, getNotApproved cs 
where cs.submitterdept = dep.csedept_id

As mentioned in the comments, alias names for tables might not be supported.

answered Jun 04 '14 at 21:43

Dan Bracuk

20,699
4
26
43

Use of QoQ here is not advised. You can condense all 2 `cfquery` calls into 1 and let the database (whose job it is to handle data like this) do its job – Scott Stroz Jun 04 '14 at 23:52

Error incorrect select list on query?

1 Answers1