Copy link to clipboard
Copied
I have a query that retrieves a UID (camp_uid) and with each camp_uid is field camp_form_fields which contains a numerical list, as an example
camp_uid: 375 , camp_form_fields: 1,4,6,7,8
camp_uid: 377 , camp_form_fields: 4,7,8
camp_uid: 390 , camp_form_fields: 9,7,6,3
camp_uid: 392 , camp_form_fields: 3,4
What I need to do is order the camp_uid into a list, so that the one which has the smallest camp_form_fields is listed first, followed by the next smallest etc, so that the example data above would give the result
camp_list = 392,377,390,375
I guess there is no easy way to get SQL to do it and I've been pondering on how to do this in CF without having too many queries within a loop
Perhaps there is somebody out there that has had to do something similar before? Appreciate any ideas
Thanks
Mark
I got it!! That's a great solution, thanks again for all of your help
Code:
<!--- GET THE LIST --->
<CFQUERY name="GetFieldList" DATASOURCE="#datasource#">
SELECT 1000 AS TempValue,camp_form_fields,camp_uid
FROM campaigns
WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)
</CFQUERY>
<!--- LOOP OVER CHECKING LENGTH OF EACH ITEM IN LIST AND CHANGE THE VALUE IN THE QUERY --->
<CFLOOP QUERY="GetFieldList">
<CFSET GetFieldList.TempValue = #Len(GetFieldList.camp_form_fields)#>
</CFLOOP>
<!--- QUE
...Copy link to clipboard
Copied
write your query so that it contains constant integer, such as 1000, in the select clause. Then loop through the results and change that field based on the the value of camp_list. Then do a Q of Q with an order by clause. Your answer will be the valuelist if camp_uid from this last query.
Copy link to clipboard
Copied
Thanks again for the assistance
Unfortunately, my SQL skills are not that advanced, to be perfectly honest I'm not quite sure what you mean about the constant integer within the query. I can do Q of Q, so if I saw a rough example of what you are thinking maybe I can figure it out
Thanks
Mark
Copy link to clipboard
Copied
select 1000 NumberToBeChangedLater
from atable
where etc
Copy link to clipboard
Copied
OK, I changed the results of the query using a loop, by assigning the length ot each list to that number, and then I run a query over that query, somebody showed me how to do that a while ago, but I can't remember how to query over an existing query that is in memory, how do I do that? I know it's simple, when you know how
Looks like I am almost there, great solution with little code.
SELECT 1000 AS TempValue,camp_form_fields,camp_uid
FROM campaigns
WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)
<CFLOOP QUERY="GetFieldList">
<CFSET GetFieldList.TempValue = #Len(GetFieldList.camp_form_fields)#>
</CFLOOP>
<br>
<CFDUMP VAR="#Getfieldlist#">
results:
query | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
RESULTSET |
| ||||||||||||||||||||||||
CACHED | false | ||||||||||||||||||||||||
EXECUTIONTIME | 0 | ||||||||||||||||||||||||
SQL | SELECT 1000 AS TempValue,camp_form_fields,camp_uid FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379) |
query | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
RESULTSET |
| ||||||||||||||||||||||||
CACHED | false | ||||||||||||||||||||||||
EXECUTIONTIME | 0 | ||||||||||||||||||||||||
SQL | SELECT 1000 AS TempValue,camp_form_fields,camp_uid FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379) |
Copy link to clipboard
Copied
I got it!! That's a great solution, thanks again for all of your help
Code:
<!--- GET THE LIST --->
<CFQUERY name="GetFieldList" DATASOURCE="#datasource#">
SELECT 1000 AS TempValue,camp_form_fields,camp_uid
FROM campaigns
WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)
</CFQUERY>
<!--- LOOP OVER CHECKING LENGTH OF EACH ITEM IN LIST AND CHANGE THE VALUE IN THE QUERY --->
<CFLOOP QUERY="GetFieldList">
<CFSET GetFieldList.TempValue = #Len(GetFieldList.camp_form_fields)#>
</CFLOOP>
<!--- QUERY THE ORIGINAL QUERY AND USE ORDER TO PULL RESULTS BASED ON THE LENGTH --->
<CFQUERY dbtype="query" name="SortResults">
SELECT *
FROM GetFieldList
ORDER BY TempValue
</CFQUERY>
Copy link to clipboard
Copied
OK I wrote this test script, with the results below.. not sure what you mean for the rest of it? I CFLOOP QUERY="queryname" but what am I supposed to do with the results?
SELECT 1000,camp_form_fields | |
FROM campaigns | |
WHERE camp_uid IN (370,372,373,374,375,376,377,378,379) |
query | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
RESULTSET |
| ||||||||||||||||||
CACHED | false | ||||||||||||||||||
EXECUTIONTIME | 0 | ||||||||||||||||||
SQL | SELECT 1000,camp_form_fields FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379) |
Copy link to clipboard
Copied
First, you have to include the camp_uid in your select clause. Next, when you are looping through the query, set computed_column_1 to the listlen of camp_form_fields, or whatever criteria you use to determine it's length.