Query of Qeries Syntax problem

Report · Aug 27, 2008

I'm getting an error on the syntax for my SELECT statement in a Query of Queries. The code is:

<cfquery name="OnOrderLogData" dbtype="Query" result="result3">
SELECT *,
ROW_NUMBER() OVER([ORDER] [BY] RowNumber [ASC]) + 0 AS PageOrder
FROM OOLogView
WHERE RowNumber > 3 AND RowNumber <= 33
</cfquery>

The error is:
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct.

I know that certain reserved words need to be escaped with square brackets, and I have surrounded all the words that were on the list in the reference manual with square brackets. I did not see anything in the reference manual that says I can not use parenthesis in the SELECT statement, nor anything that says I can not use the SQL function ROW_NUMBER. I have spent several hours trying many different things and still can't get it to work. It works fine when I execute it as an Ad Hoc query in SQL Server 2005 Studio Express (when I take out the square brackets).

Can anyone please help me figure out what is wrong and how to fix it? Thanks.

Report · Aug 27, 2008

> nor anything that
> says I can not use the SQL function ROW_NUMBER

It's not an *SQL* function, it's a T/SQL function which is specific to SQL
Server.

You shouldn't expect CF's query engine to support vendor-specific
functions. In fact the QoQ engine only implements a small subset of ANSI
SQL, so you shouldn't expect too much of it at all, to be frank.

The docs for QoQ cover *all* of what it supports. If it ain't
*specifically* in there: it's not supported.

--
Adam

Report · Aug 27, 2008

quote:

Originally posted by: Newsgroup User
It's not an *SQL* function, it's a T/SQL function which is specific to SQL
Server.

Acutally, ROW_NUMBER() OVER... was formalized in the SQL:2003 specification. Not all vendors support it, however.

That said, Q of Q syntax is bare-bones. If it's not listed as supported, it isn't supported.

Edit:
With the CFMX 6.1 documentation, they released a BNF for Query of Queries syntax. This is a good starting point:
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/using_39.htm#wp1175636
Here's the CFMX 7 documentation on using Query of Queries:
http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFu...
The CFMX 8 documentation is pretty stripped-down compared to what was available in the 6.1 and 7 documentation.
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_7.html#1137985

Report · Aug 27, 2008

> Acutally, ROW_NUMBER() OVER... was formalized in the SQL:2003 specification.
> Not all vendors support it, however.

Cheers for the heads-up on this. I just Googled it and saw all the first
few matches were for SQL Server, and cited it as being a T/SQL function.

--
Adam

Report · Aug 27, 2008

quote:

I did not see anything in the reference manual that says I can not use parenthesis in the SELECT statement, nor anything that says I can not use the SQL function ROW_NUMBER.

Q-of-Q is a rather limited subset of SQL, and does NOT support SQL Server functions such as ROW_NUMBER() See Query of Queries user guide for what is suypported. It is NOT a based on SQL Server or Oracle, so don't expect to be able to do things with Q-of-Q that you are used to doing with a full RDBMS.

Phil

Report · Aug 27, 2008

quote:

Originally posted by: Ted Schuler

I did not see anything in the reference manual that says I can not use parenthesis in the SELECT statement, nor anything that says I can not use the SQL function ROW_NUMBER.

Why would you expect any sql server function to work anywhere outside of sql server?

Report · Aug 27, 2008

Well, EXCUSSSSSSEEEEE MEEEEE!!! You folks don't need to be so snotty. We're not all experts. I just started learning Cold Fusion a few months ago on my own. I thought this was supposed to be a forum to help people. A little civility would go a long ways to make the experience pleasurable.

Report · Aug 27, 2008

quote:

You folks don't need to be so snotty.

Snotty? I thought that I answered your question, gave some background information, and pointed you to a resource. Where is that "snotty", unless you mean some of you folks, and not just plain "you folks".

Phil

Report · Aug 27, 2008

> Well, EXCUSSSSSSEEEEE MEEEEE!!! You folks don't need to be so snotty. We're
> not all experts. I just started learning Cold Fusion a few months ago on my
> own. I thought this was supposed to be a forum to help people. A little
> civility would go a long ways to make the experience pleasurable.

I think all the responses here have been simply matter-of-fact. I think
everyone here has a "real" job they're supposed to be doing, and just help
people with their issues in their spare time. So sometimes the responses
might be... well... matter-of-fact... and not necessarily dollied up with
all the (let's be honest: not really necessary) niceties one might expect
with paid-for support or a general conversation over a beer.

It might perhaps help if you consider that the written medium has the flaw
that it lacks a quite a lot of the nuance of spoken word, as well as the
fact that technical IT people aren't often the most articulate people at
the bets of times, especially when there's also a chance that your primary
language is not their primary one (I think everyone on this thread is EFL
though).

You should perhaps stop and think about the fact that you asked your
question and got four accurate and helpful reponses within two hours of
asking the question. If anything *your* reaction is that which is out of
line. I'm sure no-one minds though. I don't. "You're welcome".

--
Adam

Report · Aug 27, 2008

> (I think everyone on this thread is EFL
> though).

Not that what I just wrote was a good example of that. Bloody hell.
Serves me right for not proof-reading / scanning what I type.

--
I think perhaps Adam

Report · Aug 27, 2008

Adam, your's was the least impolite of the first three responses. But it was the next two responses that really set me off. When I see responses like, "so don't expect to be able to do things...", "Why would you expect ...", and capitalized NOT's, the meaning is condescending. We are not all experts, that is why we are here for help. I am less familiar with SQL Server 2005 than I am with Cold Fusion. I know just enough to get me by to do what I want. I didn't know these commands were specific to SQL Server.

I have Ben Forta's 3 volumes, the CF Reference Manual and the CF User's Guide. None are very specific about what IS OK to do, they are more specific about what you can not do with Query of Queries. Heck, if I would have read something in those references about what I was doing, I wouldn't have asked the question here.

If I would say something to my boss such as, "You shouldn't expect..." or "What do you expect..." in the context as these answers, I would be in the dog house. That is the kind of criteria used to judge the appropriateness of how one should converse with other people. Unfortunately, on the internet, where people can be somewhat anonymous, that does not happen very frequently. That is why I have stopped visiting all of my "for entertainment" forums that I used to read in the old days.

Report · Aug 28, 2008

You have got to be kidding me. I made "NOT" upper case for emphasis, not to imply anything, since it was clear from your original post that you were expecting Q-of-Q to behave in the same wasy as SQL Server, and I wanted to make sure that it was clear that this should not be the expectation. Sorry that you have such a thin skin that you would read anything into that. You might consider growing a thicker shell if you expect to interact in the real world.

quote:

That is why I have stopped visiting all of my "for entertainment" forums that I used to read in the old days.

This is a clue that the problem may actually lie with you and not with everyone else.....

Phil

Report · Aug 28, 2008

quote:

This is a clue that the problem may actually lie with you and not with everyone else.....

No, this does not imply there is something wrong with me. Only illogical minds would think that.

Everyone in this country keeps saying, "Why can't we all just get along?" It all starts with civil discourse. It's no wonder there is so much hate and fighting going on around the world when you can go to many, many of the online forums and blogs and see all the uncivil writing. If you haven't seen it, then you are blind.

Yes, I thought the the QofQ would work like an SQL statement would work in SQL Server, because in all the places I read about it, I didn't see anything that would give me any other impression. But 'thinking' it would work the same and 'expecting' it to work the same is a nuanced difference. Expecting implies a condescension that because I think it should be that way, it better be that way.

Report · Aug 28, 2008

quote:

Expecting implies a condescension

Oh man... what is wrong with you? I have never seen anyone read so much into nothing before, unless you are either hypersensitive or paranoid or both.

Phil

Report · Aug 28, 2008

>> Expecting implies a condescension

>Oh man... what is wrong with you? I have never seen anyone read so much into nothing before, unless you are either hypersensitive or paranoid or both.

I gotta agree with Phil on this one. All the attitude here is just in your
mind.

--
Adam

Report · Aug 27, 2008

> Adam, your's was the least impolite

Heh. Talk about "damning with faint praise".

> I have Ben Forta's 3 volumes, the CF Reference Manual and the CF User's Guide.
> None are very specific about what IS OK to do,

I actually find livedocs to be the best resource for looking up CF stuff.

http://livedocs.adobe.com/coldfusion/8/using_recordsets_1.html

Or googling these forums (NB: *Google*, not the in-built search function,
which is rubbish).

> If I would say something to my boss such as, "You shouldn't expect..." or
> "What do you expect..." in the context as these answers, I would be in the dog
> house.

Sure. But you're not our boss. If we were to draw parallels like that,
it'd be the other way around if anything. (I would not draw the parallels
either way, that said). I actually *would* talk to one of my subordinates
like that, but I'm noted at work for my "terseness" at times (some might
describe it in harsher terms than that ;-). We've all got different
expectations, I guess.

> That is the kind of criteria used to judge the appropriateness of how
> one should converse with other people.

OK, well I think - for the reasons you outlined - you're going to be a bit
disappointed there. I recommend ignoring the tone (or at least the tone
you're perceiving), and just take the info as given. On the whole, people
here are pretty polite, actually.

But anyway. I think you got your answer, so job done. Although now you've
got to work out another way of sorting out your issue...

--
Adam