QofQ Limitation?

[WestSide]

I have this master query that I want to run a sub-query on.  It works fine until I add a sub-select within my subquery..  This seems to be a limitation of how QofQ is built.  Are there any alternate solutions or does anyone have any insight.  I essentially want to do a sub-select in the query that I'm using against my master query.

Psuedo code...

<!--- Sub Query --->

<cfquery name="result" dbtype="query">

  SELECTt col1, col2, (

                                 SELECT Count(*) FROM tblName

                                ) as numWidgets

  FROM masterquery

</cfquery>

How can I achieve the net result, I'm fine doing it another way if can be  done

Any help appreciated..

-ws

[Jochem van Dieten]

Your subquery is not correated, so the following should work:

<!--- Sub Query --->
<cfquery name="q1" dbtype="query">
    SELECT Count(*) as totalCount FROM tblName
</cfquery>

<cfset tCount = q1.totalCount />
<cfset arrNew = arrayNew(1) />
<cfloop query="masterquery">
    <cfset arrayAppend(arrNew, tCount) />
</cfloop>
<cfset result = Duplicate(masterquery) />
<cfset queryAddColumn(result, "numWidgets", arrNew) />

Your example probably makes more sense if it is correlated. Post the full query code.

Message was edited by: Jochem van Dieten

[Adam Cameron.]

One cannot do a subquery in a QoQ, so that's your problem.

QoQ is fairly restricted in its SQL support.

It's all documented here:

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html

--

Adam

[WestSide]

Thanks for the quick replies.  So yes, I was trying to do a sub-query in a QoQ.      Since that does not appear to be supported what alternatives are there?

Here is the SQL I'm trying to run against my master query.  I think it is correlated?

      
            SELECT DISTINCT  c.city, u.state_code, (
                SELECT count(*) FROM UserTable
                WHERE city_id = u.city_id
                ) as numProfiles

            FROM UserTable u
            LEFT JOIN City C on c.city_id = u.city_id
            WHERE u.state_code <> ''

Any help appreciated on achieving the net result

-ws

[Jochem van Dieten]

Yes, this subquery is correlated (i.e. you have some WHERE in the subquery that tells how rows from the subquery relate to outer rows). The subquery isn't the problem, you can easily flatten that into a join. It is the left join that isn't going to work.

[WestSide]

.

[Jochem van Dieten · Accepted solution]

No, I am saying you can flatten your subquery. This will give the same result:

SELECT u.state_code, count(*) as numProfiles

FROM UserTable u,

GROUP BY u.state

What you can't do is a LEFT JOIN against your city table.

[WestSide]

There's an error in that SQL, it should be referencing the master query as opposed to UserTable, in any case, it looks like I can't use subqueries in QofQ so I'm essentially looking for an alternative that may work.

-ws