CODEWITHSUNDEEP
coldfusion
Ruchi Matharu
Ruchi Matharu

Reputation: 51

Removing null from a value list

I have a query :

<cfif topic NEQ "">
    <cfquery name="queryTopicName" datasource="#ODBC#">
        select topic as topicName from ltbTopics where topicId in (#topic#)
    </cfquery>
    <cfset selectedRiskCategories = ValueList(queryTopicName.topicName)>
</cfif>

Here the "#topic#" contains a list whose first value is empty, so it comes likes ,,51,52, etc so it gives an error as:

"Incorrect syntax near ','

.
The error occurred on line 33", can any one help me in this how to resolve this?

Upvotes: 2

Views: 2078

Answers (2)

Ruchi Matharu
Ruchi Matharu

Reputation: 51

Thankyou for your answers the final query that worked for me perfectly is:

    <!--- Query to extract selected risk category filters --->
<cfif topic NEQ "">
    <cfset arrayTopic = ListToArray(topic)>
    <cfset topic = ArrayToList(arrayTopic)>
    
    <cfquery name="queryTopicName" datasource="#ODBC#">
        select 
            topic as topicName
        from 
            ltbTopics 
        where 
            topicId in 
            (
                <cfqueryparam
                value = "#topic#"
                cfsqltype= "CF_SQL_INTEGER"
                list = "true"
            />)
    </cfquery>
    <cfset selectedRiskCategories = ValueList(queryTopicName.topicName)>
</cfif>

Once again thanks for your help, I really appreciate it.

Upvotes: 1

CFML_Developer
CFML_Developer

Reputation: 1605

There are many ways to do this.But a simple hack is convert the list to an array and then back to list.

<cfif topic NEQ "">
 <cfset arrayTopic = ListToArray(topic)>
 <cfset topic = ArrayToList(arrayTopic)>
 <!---you may need some more validations as it is possible that original list only has commas in it--->
   <cfquery name="queryTopicName" datasource="#ODBC#">
      select topic as topicName from ltbTopics where topicId in (#topic#)
   </cfquery>
   <cfset selectedRiskCategories = ValueList(queryTopicName.topicName)>
</cfif>

Upvotes: 3

Related Questions