Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to pass parameters to subquery in a query using cfquery?

Posted on 2010-11-07
8
Medium Priority
?
1,027 Views
Last Modified: 2013-12-24
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.Quantity) AS Units, Sum(tblAppMeasures.RebateAmt) AS Amount, Sum(tblMeasures.kWhSavingsPerYear*tblAppMeasures.Quantity) AS SumOfKWhSavings, Sum(tblMeasures.ThermSavingsPerYear*tblAppMeasures.Quantity) AS SumOfThermSavings, Sum(tblMeasures.DollarSavingsPerYear*tblAppMeasures.Quantity) AS SumOfDollarSavingsPerYear, Count(tblApplication.ApplicationID) AS CountOfApplicationID
FROM qryProgramApplCt, tblMeasures INNER JOIN (tblApplication INNER JOIN tblAppMeasures ON tblApplication.ApplicationID = tblAppMeasures.ApplicationID) ON tblMeasures.MeasureID = tblAppMeasures.MeasureID
WHERE tblApplication.AppStatusID=2 AND tblApplication.programID=6
AND qryProgramApplCt.programID=6
AND ((tblApplication.DecisionDate) 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.  
0
Comment
Question by:robinsf
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 34080782
It's been a re-a-lly long time since I did it, but ... IIRC you need to cfstoredproc if you want to use parameterized queries.  But it *might* also work with cfqueryparam. So try that first. ie Wrap your CREATEODBCDATE's in a cfqueryparam.

     ...
     AND ((tblApplication.DecisionDate) Between
     <cfqueryparam value="#CREATEODBCDATE(Attributes.date1)#" cfsqltype="cf_sql_timestamp">
     AND
     <cfqueryparam value="#CREATEODBCDATE(Attributes.date2)#" cfsqltype="cf_sql_timestamp">
     ...

If that doesn't work you'll have to try cfstoredproc


0
 
LVL 63

Expert Comment

by:Zvonko
ID: 34082197
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.

0
 

Author Comment

by:robinsf
ID: 34082223
I can see the attributes but they're not getting passed down to the subqueries.  
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 63

Expert Comment

by:Zvonko
ID: 34082266
I do not see why you name it SUBquery?
What is the sub-query in upper SELECT statement?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 34083644
>> qryProgramApplCt requires the begin/end date parameters (date1/date2) but
>> does not recieve it

You have to pass in any required parameters with either cfstoredproc/cfprocparam (or possibly cfqueryparam).



0
 

Author Comment

by:robinsf
ID: 34090224
To Zvonko -- qryProgramApplCt.PgmApplCt  is the Subquery in the Select statement at the top.  It also contains another subquery.
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 34094941
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')


0
 
LVL 52

Expert Comment

by:_agx_
ID: 34095035
@robinsf  -

What were the results when you tried cfqueryparam and cfstoredproc?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question