Solved

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

Posted on 2010-11-07
8
1,021 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
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 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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
how to generate a csr to request an intermediate ca on os x 3 91
DNS, website, godaddy 6 101
Moving web servers into a DMZ? 3 83
cookies analysis tools 2 110
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
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…

738 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