Creating a list that SQL can search using IN

[ACS LLC]

I have to create a list from a previous query and also append another value to the list.

To test I created the list manually, with some basic code

<CFSET country_list="">
<CFSET country_list = listappend(country_list,'''AU''')>
<CFSET country_list = listappend(country_list,'''99''')>

And then ran the query to search for records that contain either AU or 99

SELECT DISTINCT(campcountry_camp_uid)
FROM campaign_countries
WHERE campcountry_country_short IN ('#country_list#')

However, I'm having trouble with the SQL because it does not like the way I have created thelist, so a CFDUMP shows this

SELECT DISTINCT(campcountry_camp_uid) FROM campaign_countries WHERE campcountry_country_short IN ('''AU'',''99''')

This does not retrieve any records. I've tried all sorts of ways of creating the list and tweaking the SQL but can't seem to get it right.

Appreciate any pointers

Thanks

Mark

[ilssac]

Have you tried this one:

<CFSET country_list="">
<CFSET country_list = listappend(country_list,''U')>
<CFSET country_list = listappend(country_list,'99')>

SELECT DISTINCT(campcountry_camp_uid)
FROM campaign_countries
WHERE campcountry_country_short IN (<cfqueryParam value="#country_list#" list="yes" cfsqltype="cf_sql_varchar">)

[ACS LLC]

Thanks Ian, you hit the nail on the head right away

I didn't realize what I was getting into with this, I thought it was as simple as passing it the necessary info, even when it looked like it was in the same format 'AU','99' by CFOUTPUT, which was the same as hard coding it into the SQL it did not work, strange.

I just need to put a query in with a variable I add to the list using code, which I don't think will upset what you did, and I'm good

Glad I broke down the query and tested each part as this is a query, within a query, within a query!

Thanks

Mark

[ilssac]

ACS LLC wrote:

I just need to put a query in with a variable I add to the list using code, which I don't think will upset what you did, and I'm good

You will probably want to be aware of the valueList() function.  It is very useful to turn a query column into a comma delimited list.

ACS LLC wrote:

Glad I broke down the query and tested each part as this is a query, within a query, within a query!

YUCK!  It is constructs like that which give CFML such a bad name in some programmers circles.  I would give it a 98% chance that the code could be refractored to remove the nested query ugliness.

[ACS LLC]

I am sure a SQL expert could redo it, it works and it seems fast enough -->

<CFQUERY NAME="GetCampaignList" DATASOURCE="#datasource#">
SELECT TOP #val(url.icons)# campaign_categories.campcat_campaign

,campaigns.camp_icontext,campaigns.camp_alt_text

FROM campaign_categories INNER JOIN campaigns
ON campaign_categories.campcat_campaign = campaigns.camp_uid

WHERE campcat_category_uid IN (#url.cat#)
AND campcat_campaign NOT IN (#url.offerid#)

AND campaigns.camp_status = 1

AND campcat_campaign IN
(

SELECT DISTINCT(campcountry_camp_uid)
FROM campaign_countries
WHERE campcountry_country_short IN (<cfqueryParam value="#country_list#"

list="yes" cfsqltype="cf_sql_varchar">)

)

Order by newid()
</CFQUERY>

[ilssac]

Well that is a compound query, which has its uses.  I don't know your system well enough to know if it is being used well but the syntax is not necessarily bad.

I was affraid when you said query in a query in a query, you where working with a common newby constuct, something like this monstrostiy.

<cfquery name="qry1"...>
...
</cfquery>
<cfoutput query="qry1">
  <cfquery name="qry2"...>
    ...
   something = #qry1.aColumn#
  </cfquery>
   <cfoutput name="qry2">
      <cfquery name="qry3"...>
        ...
       somethingElse = #qry2.bColumn#
     </cfquery>
    </cfoutput>
</cfoutput>

[ACS LLC]

ahh. got it.. no. I avoid those types of systems at all costs, ok for a quick local test at best, as you know increadibly inefficient that way, thankfully I'm beyond that

Thanks for the help, will build it into the other queries now

mark

[ACS LLC]

the bit that confuses me is where I can use an IN on the URL. variables, so if I have 1,2,3 in there it works, but if I construct my own

and run it, it would not work, maybe numbers vs alphanumerics cause it to behave very differently.

[ilssac]

When you create it yourself, make sure you are not adding any extra delimiters like single quotes.  The <cfqueryparam....> will properly provide quotes if requried by the cfsqltype paramter you provide.  Thus the second point it to make sure you provide the correct cfsqltype for the data type of the field you are working with.

[ACS LLC]

I tried every combo possible, I even CFOUTPUT the variable to see if it looked like a working hard coded version and it did. strange

[ilssac]

Just realize that outputing it to a browser will apply HTML transformations to it, which may not be how the databae would look at it.  Even though I wouldn't think this would muck with a simple list.

The real proof is the sql output you showed earlier generated by the debugging feature and|or the result property of a <cfquery...> tag.

[ACS LLC]

you woud think it would work, but it didn;t

I had it CFSET country_list = "'au','99'">

so that output shows it as 'au',''99'

which works if hard coded, but when inserted into the statement as #country_list# it threw an error, so something weird in the mix somewhere

[ilssac]

ACS LLC wrote:

I had it CFSET country_list = "'au','99'">

Don't put the quotes in the list.  You are putting quotes around all the values, and the <cfquerparam....> tag is putting quotes around all the values.  You are getting too many quotes around each value.

[ACS LLC]

what I was trying was to get it going without cfqueryparam, by manually creating a list and then just using IN, so the single quotes were correct, if I hard coded it, with the same string, it worked. not the end of the world as I have these work arounds now, I'm just more curious as to why it did not work

[ilssac]

If you are going to hand code it --- I am really trying to push the <cfqueryparam.....> solution because it is the superior solution --- you have to use the preserveSingleQuotes() function.

<cfquery...> escapes all single quotes in variables directly used in the SQL by default.  If you want the opposite, then you have to use the preserverSingelQuotes() function around the variable name.

I.E.

WHERE campcountry_country_short IN ('#preserveSingleQuotes(country_list)#')

Just know that is syntax ripe for a SQL injection attack!.

[Adam Cameron.]

what I was trying was to get it going without cfqueryparam,

Why would you want to do that?  One should always pass dynamic values as params rather than hardcoding them into the SQL string if possible (and if "not possible", refactor the code so it is possible, IMO).

--

Adam

[ACS LLC]

now I've come to play with this I think there might be a slightly easier way

I set the country_list so that it had all the countries that I retrieved from the query, and I also added 99

<CFSET country_list = #valuelist(getcountry.countryshort)# & ",99">

Then I used your CFQUERYPARAM approach

SELECT DISTINCT(campcountry_camp_uid)
FROM campaign_countries
WHERE campcountry_country_short IN (<cfqueryParam value="#country_list#"

list="yes" cfsqltype="cf_sql_varchar">)

However, what I'm thinking is that if I can can APPEND the 99 into the query result set, then I might be able to use a simple valuelist, which will probably look something like this

SELECT DISTINCT(campcountry_camp_uid)
FROM campaign_countries
WHERE campcountry_country_short IN ('#valuelist(GetCountry.countryshort)#')

the question is, how do I append the 99 value into the query result set? I've done it before, but can't remember how.

Thanks

Mark

[ilssac]

queryAddRow() and querySetCell().

[ACS LLC]

what's the syntax for using that in CF to append that 99 value??

Thanks

Mark

[ilssac]

<cfset queryAddRow(myQry,1)>

<cfset querySetCell(myQry.recordCount,"aColumn","SomeValue")>

If I have the syntax wrong, I sugest a quick look at the documentation, because I didn't look.

[ACS LLC]

ok, thanks, I'll dig around the docs. I thought when I did it, I used a different method, but can't find my old code

Mark