Need Help with CFC That Requires URL Vars

Report · Feb 25, 2007

After finally upgrading to CFMX-7, am writing my first CFC. CFC consists of/contains a query, whose arguments can be passed as URL variables (the CFC is called from a drill-down program). Only problem is, the CFC's query contains the following WHERE statement:
WHERE (sales_code = '#ARGUMENTS.sales_code#') AND
(location IN
<CFOUTPUT QUERY = "GetLocations" maxrows="1">
(#QuotedValueList(GetLocations.location)#)
</CFOUTPUT> ) AND
(more where statements using #ARGUMENTS.whatever#)

Problem is HOW TO HANDLE the #QuotedValueList(GetLocations.location)#
This "location list" is populated by a query that runs BEFORE this query, depending on what REGION the user selected on the report's main form (there are 20 regions avail in the form's drop-down list). If the user selected, for example, the EAST REGION, the first query would populate the #QuotedValueList(GetLocations.location)# with all the locations in that region.

So I'm not sure how to set up the first query so it can pass, as an argument, the values in the location list. I don't think CFC's can contain URL variables, nor can they contain queries that don't start with <CFCOMPONENT> and <CFFUNCTION>.

Thought about setting up the first query as a UDF, contained inside the .CFM file that's calling the CFC. But then not sure if I can pass the UDF's return value as a "list of locations" (i.e., #QuotedValueList(GetLocations.location)# ) as an ARGUMENT for the CFC's query/WHERE statement.

Hope this makes sense. The UDF and CFC examples provided in my CF Developer's journals and other CF books are very basic examples on using UDFs and CFCs, nothing quite this complex. Or maybe my brain is making it a bigger deal out of this, than it really is.

Any help/advice on how to "structure" something like this would be much appreciated (e.g., PUT GetLocations query in main .CFM file, how to get the location list into the CFC's query, etc. And can you use something like
 #QuotedValueList(ARGUMENTS.location)#, in place of #QuotedValueList(GetLocations.location)#, and if so, how do you pass the argument's value to the CFC?

Thanks for any help/advice.
Gary.

Report · Feb 25, 2007

I don't see why you need two queries.

Report · Feb 25, 2007

You would see if you didn't assume what the other person was trying to do. You're the person who makes assumptions about what others are trying to do, without really knowing. I wouldn't be asking the question if the first query wasn't needed. I'd love to see your simplified, "don't need two-queries" version, that can run any of these hundreds of single locations, 25 region/sub-region combos, and "areas", all with a simple user-front end. All the user has to do on the form is select a location, region or area, and month/year. The 2 queries do all the work.

The user can select a single location, a region, or an area, over 25 combos from the report's main form. The beauty o the query is that, regardless whether the user wants a single location report, one of dozens of region or sub-region reports, or another sub-set type of region (area), the first query always selects the correct locations for the main (2nd) query to run it's totals and calculations on.

Here's a copy of the first query, with some item's "XXX"d" out for confidentiality. When the query runs, everything you need is all wrapped up in a nice little list called GetLocations.location. Allowing the main query to run any of 150 single locations, 25 regions, sub-regions, and other area-types (frequently changing).

<CFIF '#url.RepType#' EQ 'location'>
<CFSET location = '#url.location#'>

<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE xxx = '#location#'
ORDER BY location
</CFQUERY>
<CFELSEIF '#url.RepType#' EQ 'region'>
<CFSET location = '#url.location#'>

<CFIF #location# IS "NORTH AMERICA">
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE region = 'xxxxxxx OPERATIONS' or
region = 'XXX' or
region = 'XXX'
ORDER BY location
</CFQUERY>
<CFELSEIF #location# IS "INTERNATIONAL">
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE region = 'ATLANTIC' or
region = 'PACIFIC'
ORDER BY location
</CFQUERY>
<CFELSEIF #location# IS "EAST">
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE subregion IN('EAST LINE','CARIBBEAN')
ORDER BY location
</CFQUERY>
<CFELSEIF #location# IS "WEST">
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE subregion IN('WEST LINE','MEXICO','SOUTH AMERICA')
ORDER BY location
</CFQUERY>
<CFELSEIF #location# IS "XX MG DIR">
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE subregion IN('XXX METRO','XXX METRO','XXX METRO','XXX METRO')
ORDER BY location
</CFQUERY>
<CFELSEIF #location# IS "XXXXXXXX">
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE xxx IN('AAA','BBB','CCC','DDD','EEE','FFF')
ORDER BY location
</CFQUERY>
<CFELSEIF #location# IS "SYSTEM">
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE division IN('NORTH AMERICA','INTERNATIONAL')
ORDER BY location
</CFQUERY>
<CFELSEIF (#location# IS "xxxxxxx OPERATIONS") OR (#location# IS "XXXX")>
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE region = '#location#'
ORDER BY location
</CFQUERY>
<CFELSE>
<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE subregion = '#location#'
ORDER BY location
</CFQUERY>
</CFIF>
<CFELSE>
<CFSET location = Mid(url.location,1,3)>

<CFQUERY name="Getlocations" datasource="MyDBSQL">
SELECT xxx as location
FROM location_Region_Table
WHERE xxx = SUBSTRING('#location#',1,3)
ORDER BY location
</CFQUERY>
</CFIF>

Report · Feb 25, 2007

quote:

Originally posted by: Gary1
You would see if you didn't assume what the other person was trying to do. You're the person who makes assumptions about what others are trying to do, without really knowing. I wouldn't be asking the question if the first query wasn't needed. I'd love to see your simplified, "don't need two-queries" version, that can run any of these hundreds of single locations, 25 region/sub-region combos, and "areas", all with a simple user-front end. All the user has to do on the form is select a location, region or area, and month/year. The 2 queries do all the work.

The table you are selecting from in your second query, you sure you can't join to the location_region_table on the location field? If you can, you only need one query. If you can't, you might have referential integrity issues.

Report · Feb 25, 2007

user select a region in your form -> the form is submitted -> on the
action page your cfc is invoked and passed to the selected region as one
of the agruments -> your GetLocations query is inside your cfc (inside
one of the cffunctions), is executed, then you set <cfset this.loclist =
valuelist(getLocations.location)> -> inside your second query in the cfc
just use ... IN (listqualify(loclist, "'")) ...

note: you CAN run regular queries inside cfcs.
--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Report · Feb 25, 2007

Not sure I'm understanding this correctly, but here goes.

As the location query executes first (with a passed parameter) this can either be a cfquery or another cfc which is called from this cfc.

But as dan has said you can use just the single query. lets assume the location query is as follows

Select location From locationTable where locationID = #arguments.locationID#

Then you can do thisWHERE (sales_code = '#ARGUMENTS.sales_code#')
AND location IN (Select location From locationTable where locationID = #arguments.locationID#)

But you could have

<cfquery name="GetLocations">
Select location From locationTable where locationID = #arguments.locationID#
</cfquery>

Then your next query
<cfquery...............>
WHERE (sales_code = '#ARGUMENTS.sales_code#') AND
(location IN (#QuotedValueList(GetLocations.location)#)
AND (more where statements using #ARGUMENTS.whatever#)
</cfquery>
ken

Adobe Community

Need Help with CFC That Requires URL Vars