Solved

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

Posted on 2010-11-07
8
1,017 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

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 …
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now