Solved

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

Posted on 2010-11-07
8
1,019 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 500 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 28

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Website being blocked? 3 126
PHP Register global 21 87
web & database SERVERS -- PHYSICAL & VM ? 5 55
Adding multiple JVM environments to RedHat 6 7 18
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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