0

Using ColdFusion Server Enterprise 9,0,1,274733.

Has anyone seen this before? The following code executes without error.

<cfquery name="x" datasource="dw">
select event_code, event_name
from event
</cfquery>

<cfquery name="y" dbtype="query">
select event_code || event_name fred
, event_code
from x
</cfquery>

Two things to notice are that I declared an alias without using the keyword "as", and I used || to concatenate strings. However, if I qualify the first event code, like this:

<cfquery name="y" dbtype="query">
select x.event_code || event_name fred
, event_code
from x
</cfquery>

I get

Query Of Queries syntax error.

Encountered ". Incorrect Select List, Incorrect select column, x.event_code cannot be followed by '||'

There is a similar error if I attempt to declare an alias without the keyword "as".

For the task at hand, I can figure out what to do, but I'm curious if the same thing happens to those of you on Version 10?

Edit starts here

After reading the comments, I tried parentheses. This runs without error.

<cfquery name="y" dbtype="query">
select (x.event_code || event_name) fred
, event_code
from x
</cfquery>
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • same things happens on CF10 but works in Railo 4, but you can do `SELECT a + b AS test` in ColdFusion 10 – Matt Busche Feb 14 '14 at 18:51
  • Sounds like a bug. It works under CF9 if you enclose both in parenthesis ie `select x.event_code || x.event_name) AS fred ...`. @MattBusche - Can you see the same holds true for CF10? I am not near a CF10 install. – Leigh Feb 14 '14 at 19:05
  • @Leigh enclosing in `()` works in CF10 – Matt Busche Feb 14 '14 at 19:07
  • @MattBusche - Cool, thanks. (Edit) Darn, just noticed my previous example is missing the open parenthesis ie `select (x.event_code || x.event_name) AS fred ...` – Leigh Feb 14 '14 at 19:10
  • Anybody wanna enter an answer now that we have one? – Dan Bracuk Feb 14 '14 at 19:15
  • 1
    Curious...why would you want to use `x.` on one column name, but not another in the QoQ? What is the use case for doing this? Also, it seems as if your QoQ is simply concatenating data that already exists in the original query, why not simply concatenate it when it is being output? – Scott Stroz Feb 14 '14 at 19:19
  • 2
    The code in this question is not production code. When I encounterd the problem for real, I started troubeshooting with simple code. Once I was able to cause the error to occur, I posted the code that caused it.. – Dan Bracuk Feb 14 '14 at 19:28
  • Since joins are not supported in QoQ it seems odd that you would want to qualify column names. – Scott Stroz Feb 15 '14 at 02:55
  • 1
    QoQ's support joins, just not outer joins. – Leigh Feb 16 '14 at 14:00

1 Answers1

3

You have to wrap your statement in () for it to work correctly

SELECT (x.event_code || event_name) fred
Matt Busche
  • 14,216
  • 5
  • 36
  • 61
  • ah, i completely ignore the `x.` when I tested, so looks like @Leigh your answer was really the only correct one, sorry about that – Matt Busche Feb 14 '14 at 19:42
  • Ah, no worries. I did not feel like writing up an answer. It was more of a curiosity exercise :) – Leigh Feb 14 '14 at 20:08