Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

Help with Query O Query

I having problems with query of query's. I keep getting the following error:
Query Of Queries syntax error.
Encountered "month. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition

Any advise?
<cfquery name="qCurrYear" datasource="#application.datasource#" cachedwithin="#CreateTimeSpan(0,0,5,0)#">
SELECT
ClosingDate, YEAR(ClosingDate) as theY, MONTH(ClosingDate) as theM,
ClosingID,
ListingId,
Commission,
Bonus,
ReferralPaid
FROM
closings
WHERE
year(ClosingDate) = #currentYear# <!--- CURRENT YEAR --->
</cfquery>
 
 
/////////////2nd Query referencing the master query (above)////////////////////////
<cfloop from="1" to="12" index="m">
<cfquery name="MoComCur#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
ClosingDate,
sum(Commission) as TotComish,
sum(Bonus) as TotBonus,
sum(ReferralPaid) as TotReferral
FROM
qCurrYear
WHERE
month(ClosingDate) = #m# AND year(ClosingDate) = #currentYear#
group by
month(ClosingDate), year(ClosingDate)
</cfquery>
</cfloop>

Open in new window

0
jasch2244
Asked:
jasch2244
1 Solution
 
azadisaryevCommented:
CF QoQ has a very limited sub-set of SQL functions.
it does not support MONTH, YEAR and many other functions available in general SQL.
it does not support any database-specific functions.

instead of using these functions in your WHERE and GROUP BY clauses, you need to reference the columns (theY, theM) you have created with those functions in your original query (and do not select ClosingDate column in the QoQ - otherwise you will have to group by on it, too, which will not return the resultset you expect...).

also, since your original (master) query already limits the datatset to a specific year, you do not meed to select and use that column in your QoQ.


Azadi
<cfloop from="1" to="12" index="m">
<cfquery name="MoComCur#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
theM,
sum(Commission) as TotComish,
sum(Bonus) as TotBonus,
sum(ReferralPaid) as TotReferral
FROM
qCurrYear
WHERE theM = #m#
GROUP BY theM
</cfquery>
</cfloop>

Open in new window

0
 
Andrew MaurerCommented:

SELECT
ClosingDate,
sum(Commission) as TotComish,
sum(Bonus) as TotBonus,
sum(ReferralPaid) as TotReferral
FROM
qCurrYear
WHERE
month(ClosingDate) = #m# AND year(ClosingDate) = #currentYear#
GROUP BY ClosingDate
ORDER BY ClosingDate

Open in new window

0
 
jasch2244Author Commented:
Thank you! I was using the year reference because once I figured out what the error was I was going to combine the previous year and current year into one query (master) but due to the limitations of the Query o Queries I don't know that I'd be able to do this.  EXAMPLE:
SELECT
ClosingDate, YEAR(ClosingDate) as theY, MONTH(ClosingDate) as theM,
ClosingID,
ListingId,
Commission,
Bonus,
ReferralPaid
FROM
closings
WHERE
year(ClosingDate) = #currentYear# and year(ClosingDate) = #prevYyear#
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now