How to use Query of Queries as subquery (Coldfusion)

Question

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>

Is this a simplified version of your real query? This looks perfectly valid to me. — Andrew
– Andrew, Commented Oct 28, 2019 at 14:26
@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
– FlowLess10, Commented Oct 28, 2019 at 14:31
Well as @Andrew said it looks ok. Here is the demo : dbfiddle.uk/… — VBoka
– VBoka, Commented Oct 28, 2019 at 14:37
ROW_NUMBER() and OVER() are not supported by queries of queries. — Jason Holden
– Jason Holden, Commented Oct 28, 2019 at 14:50
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 — Miguel-F
– Miguel-F, Commented Oct 28, 2019 at 15:03

MT0 · Accepted Answer · 2019-10-28 15:03:22Z

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.

answered Oct 28, 2019 at 15:03

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shawn Over a year ago

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.

Collectives™ on Stack Overflow

How to use Query of Queries as subquery (Coldfusion)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related