[philiprakusen]

I have a simple query that returns a list of IDs that are in the GetProducts query:

SELECT company_id 
FROM companies
WHERE company_id IN (#ValueList(GetProducts.company_id)#)

The problem is the list of company ids in the GetProducts query are sometimes NULL and the above query results in the error: Incorrect syntax near ','.

The SQL looks like this:

SELECT company_id 
FROM companies 
WHERE company_id IN (302,307,235,,514,154,,421)

I have tried writing an IF statement to compile the list if the company_id is not NULL but have run into problems with commas – especially if the last record is NULL.

Does anyone know how to get the second query to ignore the NULL records or a sound alternative?

[skyhawk133]

Can you use a sub query instead of pulling the value list from another query? This would be more efficient and eliminate your problem...

[philiprakusen]

skyhawk133, on Dec 21 2004, 08:15 AM, said:

Can you use a sub query instead of pulling the value list from another query? This would be more efficient and eliminate your problem...

The GetProducts query is HUGE (!) so was hoping not to have to replicate it as a sub query.

Thanks for your suggestion.

[skyhawk133]

This may defeat the purpose since you said your query is huge, however, I just tried this on a similar situation and it works... here is just the debug output:

<!--- Original Query --->
<cfquery datasource="staff" name="list">
SELECT TOP 25 * FROM staff
</cfquery>

<!--- New query to eliminate null values for the valuelist --->
<cfquery name="newlist" dbtype="query">
SELECT nickname FROM list WHERE nickname IS NOT NULL AND nickname != ''
</cfquery>

<cfdump var="#list#">
<cfdump var="#newlist#">

<cfdump var="#valuelist(list.nickname)#">
<cfdump var="#valuelist(newlist.nickname)#">

In the first list, it outputs about 10 null values and empty strings, in the newlist (after the query-of-query) the value list is stripped of all the null values.

The above method may be just as, if not more, overhead than doing a subquery since you are putting all the work on the coldfusion server. The nice thing about SQL is... it's designed to aggregate data so doing a subquery is it's job whereas using ColdFusion is a secondary process. It's best to let the SQL server do all the work you can before bringing the data back.

[philiprakusen]

OK - New idea:

Any idea how to test for a NULL value in a list, e.g. my_list = '1,2,3,,5,6,,8,9'?

<cfif ListFind(my_list, '')>
  ...
</cfif>

Unfortunately, the above doesn't work.

[skyhawk133]

You could search for the double commas in the string?! You could also loop through the list and only echo if the value is NEQ to "". List find won't work because it only returns the first instance of the search, so if you have 5 nulls it will return the first position of the first null.

[supersloth]

loop thru the list assining all non-NULL numbers to a new list? then use that list in your valuelist function for the sql?

[SpongeC]

philiprakusen, on Dec 21 2004, 10:47 AM, said:

OK - New idea:

Any idea how to test for a NULL value in a list, e.g. my_list = '1,2,3,,5,6,,8,9'?

<cfif ListFind(my_list, '')>
  ...
</cfif>

Unfortunately, the above doesn't work.

Well, i am not quite sure on what you are trying to do. Do you want to place the NULL value between the ',,' or remove the empty list elements?

Anyway, whatever you are trying to do you can use the following function:

<cffunction name="ReplaceListBlanks" returntype="string">
	<cfargument name="lInput" required="yes" type="string">
	<cfargument name="bReplaceWithNull" required="no" type="boolean" default="true">
	
	<!--- Change the format of the list from '1,2,3,,5,6,,8,9' to ',1,2,3,,5,6,,8,9,' --->
	<cfset var lOutput = "," & arguments.lInput & ",">
	
	<cfif arguments.bReplaceWithNull>
  <!--- Replace all occurences of ',,' with ',NULL,' --->
  <cfset lOutput=REReplace(lOutput, ",,", ",NULL,", "ALL")>
	<cfelse>
  <!--- Replace all occurences of ',,' with ',' --->
  <cfset lOutput=REReplace(lOutput, ",,", ",", "ALL")>
	</cfif>
	
	<!--- Remove the first and last comma and make the string a valid list again --->
	<cfset lOutput = Mid(lOutput, 2, len(lOutput)-2)>
	
	<cfreturn lOutput>
</cffunction>

Use it as :

<cfdump var="#ReplaceListBlanks('1,2,3,,5,6,,8,9')#"><br>
<cfdump var="#ReplaceListBlanks('1,2,3,,5,6,,8,9', false)#">

... and the result will be:

Quote

1,2,3,NULL,5,6,NULL,8,9
1,2,3,5,6,8,9

[skyhawk133]

Whoa damn, bustin out with the function, very nice Yes, SpongeC's way should work for ANY list no matter if it's in a query or otherwise.

[shiroh]

What database are you using? If the db supports subqueries why not use a subquery instead of using the valuelist function.

However, a funky little work around is

#arrayToList(listToArray(valueList(Get_Products.company_id)))#

ColdFusion does not recognize empty strings (nulls) in lists, so if you convert the list to an array it will automatically remove the empty list elements, then all you do is convert the array back to the list. SpongeC's solution was quite nifty though!

HTH

[skyhawk133]

That's what I was going to do but figured the subquery or even query of query would be faster.

I agree, the subquery would be best though.

[shiroh]

I am such an idiot, I need to get more sleep sometimes! The next best thing to a subquery would be using the CFQUERYPARAM tag.

SELECT company_id
FROM companies
WHERE company_id IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" list="Yes" separator="," value="#ValueList(GetProducts.company_id)#" />)

And that's the end of that, it will solve all your problems! A handy tip is to use the CFQUERYPARAM tag as much as possible since it allows for faster query execution.

HTH

[skyhawk133]

I did NOT know there was a list parameter within query param, now THAT is cool.

Very nice shiroh! Good call!