Solved

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

Posted on 2010-11-07
8
1,020 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
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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
Google Crawl Errors Producing 404 Errors 4 84
DNS, website, godaddy 6 90
Nameserver and MX Record 2 79
change time in cron 4 89
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…
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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