1

I have this query

<cfquery datasource="Intranet" name="getMaxstars">
    SELECT   TOP (1) WITH TIES emp_id, SUM(execoffice_status) AS total_max
    FROM     CSEReduxResponses
    GROUP BY emp_id
    ORDER BY total_max DESC
</cfquery >

<cfquery datasource="phonelist" name="getEmployees">
    SELECT first_name, last_name, emp_id
    FROM employee
</cfquery>

<!--- Query of a query join --->
<cfquery name="getEmployeeStars" dbtype="query">
    SELECT getEmployees.first_name + ' ' + getEmployees.last_name AS full_name, getMaxstars.total_max AS stars
    FROM getMaxstars, getEmployees
    WHERE getMaxstars.emp_id = getEmployees.emp_id
</cfquery>

IN this query I want to get the Max of emp_id in CSEReduxResonses table, what i want to get is to ouput the full name of the employee which only employee table has and also how much stars(execoffice_status) that employee has. so the ouput would look something like this:

John doe 4

i keep getting this error:

Query Of Queries syntax error. Encountered ". Incorrect Select List, Incorrect select column, getEmployees.emp_namefirst cannot be followed by '+'

The error occurred in C:\inetpub\wwwroot\WebServer\win\test\cse_execoffice_newsletter.cfm: line 60

58 :
59 : 60 : 61 : SELECT getEmployees.emp_namefirst + ' ' + getEmployees.emp_namelast AS full_name, getMaxstars.total_max AS [stars] 62 : FROM getMaxstars, getEmployees

user3408399
  • 109
  • 1
  • 2
  • 12
  • Have you tried to rewrite this part `as [stars]`? – bjnr Apr 10 '14 at 22:07
  • What line is it erroring on? That's an error from SQL Server, not from QoQ, so I think you might be looking at the wrong bit of code..? – Adam Cameron Apr 10 '14 at 22:23
  • It doesn't seem like you're having an issue with the Query of Query, but with the other ones, either `getEmployees` or `getMaxstars`. The error says it's from SQLServer. If it was a query of query error, it'll say something like `Query Of Queries syntax error.` – Chester Apr 10 '14 at 22:23
  • I have edit with the correct error. – user3408399 Apr 10 '14 at 22:46
  • 3
    Why on earth use a QoQ for this? A single JOIN using a [derived table](http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values) would do the trick, and more efficiently. – Leigh Apr 10 '14 at 22:56
  • This looked familiar. I had a similar question once. http://stackoverflow.com/questions/21786926/inconsistent-q-of-q-behaviour. Leigh has a valid point though. – Dan Bracuk Apr 11 '14 at 12:50
  • He's using a Query of Query because those 2 original queries are from different datasources. For all we know, "phonelist" could be an Access database and "intranet" could be Oracle. (Probably not, but they could be.) – Samuel Dealey Apr 12 '14 at 16:08
  • You're showing an error message for `getEmployees.emp_namefirst` but that column name doesn't appear anywhere in your code. I see `getEmployees.first_name` though. – Samuel Dealey Apr 12 '14 at 16:11
  • *different datasources..For all we know, "phonelist" could be an Access database and "intranet" could be Oracle. (Probably not, but they could be.)* Yep, it could also be two sql server databases on the same server, which why I asked the question (on several threads). @user3408399 - Two datasources does not necessarily make a QoQ an automatic requirement. Especially in SQL Server. Depending on the permissions and environ, it is entirely possible you can query different databases via the same datasource. But it is impossible to say without more details. (Hint,hint). – Leigh Apr 14 '14 at 14:46

1 Answers1

1

Looks like it's either a bug on concatenating with aliases.

Below is a sample:

<cfset myQuery = queryNew("Column_1, Column_2", "varchar, varchar")>

<cfloop index="i" from="1" to="5">
    <cfset newRow = QueryAddRow(myQuery, 1)>    
    <cfset newCell = QuerySetCell(myQuery, "Column_1", i)>
    <cfset newCell = QuerySetCell(myQuery, "Column_2", i)>
</cfloop>
<cfdump var="#myQuery#" label="myQuery">

<hr />

<cfquery name="QoQ" dbtype="query">
    SELECT
        [myQuery].[Column_1] as Column_1,
        [myQuery].[Column_2] as Column_2
    FROM [myQuery]
</cfquery>
<cfdump var="#QoQ#" label="QoQ">

<hr />

<cfquery name="QoQ_new" dbtype="query">
    SELECT
        [myQuery].[Column_1] as Column_1,
        [myQuery].[Column_2] as Column_2,
        ([myQuery].[Column_1] + ' ' + [myQuery].[Column_2]) as stars
    FROM [myQuery]
</cfquery>

<cfdump var="#QoQ_new#" label="QoQ_new">

You can run the code sample on http://cflive.net/ and see that ColdFusion gives an error, but Railo doesn't.

Guess this won't WILL work for you.

EDIT: Base on Dan's link above, a parenthesis around the concatenated SELECT column would work:

Chester
  • 1,081
  • 9
  • 18