Participant

Question

Query of Qeries Syntax problem

Forum|Forum|17 years ago
August 27, 2008
5 replies
2522 views

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.

Database access

This topic has been closed for replies.

N

Newsgroup_User

Inspiring

> 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

2

27SpartanAuthor

Participant

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.

P

paross1

Participating Frequently

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

2

27SpartanAuthor

Participant

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.

D

Dan_Bracuk

Inspiring

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?

2

27SpartanAuthor

Participant

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.

P

paross1

Participating Frequently

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

P

paross1

Participating Frequently

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

N

Newsgroup_User

Inspiring

> 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

K

Kronin555

Participating Frequently

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=ColdFusion_Documentation&file=00001266.htm#1137985
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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.