robinsf
asked on
How to pass parameters to subquery in a query using cfquery?
I have a query that I created in MS Access. The query includes references to a subquery
that requires a begin date parameter and and an end date parameter. It works perfect in
MS Access but, when I attempt to use the query in a CFQuery block, the date parameters
don't get passed down to the subquery. The subquery also references another subquery
but that last subquery doesn't require the passed in parameters. The query is:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -----
SELECT tblMeasures.MeasureName AS Measure, qryProgramApplCt.PgmApplCt AS AppCntTotal, Sum(tblAppMeasures.Quantit y) AS Units, Sum(tblAppMeasures.RebateA mt) AS Amount, Sum(tblMeasures.kWhSavings PerYear*tb lAppMeasur es.Quantit y) AS SumOfKWhSavings, Sum(tblMeasures.ThermSavin gsPerYear* tblAppMeas ures.Quant ity) AS SumOfThermSavings, Sum(tblMeasures.DollarSavi ngsPerYear *tblAppMea sures.Quan tity) AS SumOfDollarSavingsPerYear, Count(tblApplication.Appli cationID) AS CountOfApplicationID
FROM qryProgramApplCt, tblMeasures INNER JOIN (tblApplication INNER JOIN tblAppMeasures ON tblApplication.Application ID = tblAppMeasures.Application ID) ON tblMeasures.MeasureID = tblAppMeasures.MeasureID
WHERE tblApplication.AppStatusID =2 AND tblApplication.programID=6
AND qryProgramApplCt.programID =6
AND ((tblApplication.DecisionD ate) Between #CREATEODBCDATE(Attributes .date1)# And #CREATEODBCDATE(Attributes .date2)#)
GROUP BY tblMeasures.MeasureName, qryProgramApplCt.PgmApplCt
ORDER BY tblMeasures.MeasureName;
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -----
qryProgramApplCt requires the begin/end date parameters (date1/date2) but does not recieve it.
Any idea how I can get this query to work. Thanks for everything in advance. I'm no SQL
expert (or a fully CF expert) and this one is killing me.
that requires a begin date parameter and and an end date parameter. It works perfect in
MS Access but, when I attempt to use the query in a CFQuery block, the date parameters
don't get passed down to the subquery. The subquery also references another subquery
but that last subquery doesn't require the passed in parameters. The query is:
--------------------------
SELECT tblMeasures.MeasureName AS Measure, qryProgramApplCt.PgmApplCt
FROM qryProgramApplCt, tblMeasures INNER JOIN (tblApplication INNER JOIN tblAppMeasures ON tblApplication.Application
WHERE tblApplication.AppStatusID
AND qryProgramApplCt.programID
AND ((tblApplication.DecisionD
GROUP BY tblMeasures.MeasureName, qryProgramApplCt.PgmApplCt
ORDER BY tblMeasures.MeasureName;
--------------------------
qryProgramApplCt requires the begin/end date parameters (date1/date2) but does not recieve it.
Any idea how I can get this query to work. Thanks for everything in advance. I'm no SQL
expert (or a fully CF expert) and this one is killing me.
So you say you can see the variable values for Attributes.date1/2 when you print them out with CFOUTPUT, right?
I assume you confuse Attributes.date1 with some procedure parameters in MS Access.
I assume you confuse Attributes.date1 with some procedure parameters in MS Access.
ASKER
I can see the attributes but they're not getting passed down to the subqueries.
I do not see why you name it SUBquery?
What is the sub-query in upper SELECT statement?
What is the sub-query in upper SELECT statement?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To Zvonko -- qryProgramApplCt.PgmApplCt is the Subquery in the Select statement at the top. It also contains another subquery.
With Access Query Use DateValue() function
e.g.
SELECT Issues.ID, Issues.Title
FROM Issues
WHERE Issues.[Opened Date] between datevalue('11/01/2010') and datevalue('11/09/2010')
e.g.
SELECT Issues.ID, Issues.Title
FROM Issues
WHERE Issues.[Opened Date] between datevalue('11/01/2010') and datevalue('11/09/2010')
@robinsf -
What were the results when you tried cfqueryparam and cfstoredproc?
What were the results when you tried cfqueryparam and cfstoredproc?
...
AND ((tblApplication.DecisionD
<cfqueryparam value="#CREATEODBCDATE(Att
AND
<cfqueryparam value="#CREATEODBCDATE(Att
...
If that doesn't work you'll have to try cfstoredproc