ValueList with Null Value

Forum|Forum|16 years ago
April 16, 2010
3 replies
3605 views

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?

This topic has been closed for replies.

J

jbreslowAuthor

Inspiring

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!

D

Dan_Bracuk

Inspiring

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.

BKBK

Community Expert

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

A

Adam Cameron.

Inspiring

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

Community Expert

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.

D

Dan_Bracuk

Inspiring

Modify your query to exclude those records.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded