ValueList with Null Value

[jbreslow]

Hello,

I am outputting values from a query using valueList. If one of the values in the DB is null I get an extra comma in the list. Is there a way for valueList to skip over null values OR is there a better way to make a list like this? Should I be using something like listAppend?

[Dan_Bracuk]

Modify your query to exclude those records.

[BKBK]

arrayToList(listToArray(valueList(myQuery.myColumn)))

[Adam Cameron.]

BKBK wrote:

arrayToList(listToArray(valueList(myQuery.myColumn)))

I tend to use listChangeDelims() for this, as it seems closer to the intent of the requirement, and is one fewer function calls.  But it amounts to the same thing.

Equally: what Dan suggests should be a consideration.  If you don't want those rows in the first place: don't return them from the DB.

--

Adam

[BKBK]

There is one reason why I consider my suggestion a better strategy. The question the original poster has asked is a local one. My suggestion is a local solution, with no risk of side-effects elsewhere.

Suppose you were to follow the strategy to "change the SQL in the first place to avoid null values" or "change the list delimiter". Then there is a risk that subsequent code that uses the query or the list will crash. In other words, such a change will likely lead to a cascade of further changes down the line.

However, don't get me wrong. I, too, believe a result set to be more  efficient without null values, and one function call to be more  efficient than two.

[Adam Cameron.]

You don't need to change the list delimiter, you just use listChangeDelims() to remove the blank entries, eg: listChangeDelims(listWithEmptyItems, ",")

It's leveraging the same technique you are with your list->array->list notion: that CF list functions ignore (and, on the whole: remove) empty list entries.

--

Adam

[BKBK]

Ah, get you.

[jbreslow]

I agree that ommitting the null value from the query would do the job that that isn't always an option. I suppose I could write another query to grab just the values I need from the DB but there is already a query running on the page that returns the data I need. Why hit the DB again if I don't have to?

I'll give these two pieces of code a shot. Thanks guys!

[Dan_Bracuk]

If you need data with empty records anyway, you don't need to go back to the db to filter out the blanks.   You can use Query of Queries.