2

I want to use a query of queries as a subquery but i get a syntax error: Encountered "(. Here is my query (qHistoryData is my query object): 

<cfquery dbtype="query">
    SELECT *
    FROM (
        SELECT 
            t2.*,
            ROW_NUMBER() OVER(
                PARTITION BY collectid
                ORDER BY update_on DESC
            ) AS seqnum
        FROM qHistoryData t2
    ) t
    WHERE t.seqnum = 1;
</cfquery>
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
FlowLess10
  • 133
  • 10
  • What database do you use ? Oracle ? Also, a full error would be nice. Thanks! – VBoka Oct 28 '19 at 14:26
  • 2
    Is this a simplified version of your real query? This looks perfectly valid to me. – Andrew Oct 28 '19 at 14:26
  • 2
    @VBokšić I use Oracle and my error is: Query of Queries syntax error. `Encountered "(`. I do not get anything more as an error. – FlowLess10 Oct 28 '19 at 14:31
  • @Andrew This is the real query. – FlowLess10 Oct 28 '19 at 14:34
  • 1
    Well as @Andrew said it looks ok. Here is the demo : https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=6650381bdf7a866d3b3faa8f1bb15d5c – VBoka Oct 28 '19 at 14:37
  • How are you actually executing this query? – Andrew Oct 28 '19 at 14:39
  • 1
    I use coldfusion query of queries. So `qHistoryData` is a query variable from Coldfusion – FlowLess10 Oct 28 '19 at 14:43
  • 6
    ROW_NUMBER() and OVER() are not supported by queries of queries. – Jason Holden Oct 28 '19 at 14:50
  • 4
    As others have already mentioned, ColdFusion query of queries is limited in it's SQL abilities. It is not a full blown implementation. [Here is a link that describes the syntax and it's limitations](https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/query-of-queries-user-guide.html) – Miguel-F Oct 28 '19 at 15:03
  • QoQ is _VERY_ limited. This kind of query is very easy in most DBMSes, and you can very likely get this data in your original query without having to hit the database again. More often than not, a QoQ is unnecessary if you know how to ask your db for the data. – Shawn Oct 29 '19 at 01:26
  • @Shawn Thank you for this advice. I think I managed to solve my problem now without the use of QoQ. – FlowLess10 Oct 29 '19 at 07:08

1 Answers1

4

A Query of Queries is implemented entirely at the ColdFusion application layer (in Java) and does not involve the database so you cannot use many of the functions that are available in the database.

Add a column to your qHistoryData query that calculates ROW_NUMBER() OVER( PARTITION BY collectid ORDER BY update_on DESC ) AS seqnum and then in your Query of Queries you can do:

<cfquery dbtype = "query">
SELECT *
FROM   qHistoryData
WHERE  seqnum = 1;
</cfquery>

Your other option is to manually process the query object and remove the unwanted rows.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Given the data that OP already has, this is a good way to get the desired results. However, I still think this is probably data that can be returned from the original query without having to incur the overhead of a QoQ. – Shawn Oct 29 '19 at 01:29