Thanks for your suggestions.
You're right with the "is" versus "=" for NULL values. Unfortunately
the coding for the mapserver does not allow an "IS" statement.
Concerning the coalesce(datafield,-9999) it seems rather unusable for
me, if I have to explicitly stated each column, as a) for many tables
I have different column titles and b) there are up to 60 columns for
each table. There is no way to say something like
coalesce(table.*, -9999)
I guess, no?
Thanks a lot!
Stefan
Once again Martijn is correct, and you have to use "is null" not
"=null"
One thing you might want to consider is adding -9999 as a default
value in the table so that when new data is entered it with a null
it automatically gets the correct value.
Using coalesce with the value will probably be the simplest for you.
Try select ...,coalesce(datafield,-9999) as datafield,... and that
will change all nulls to -9999 and give it the correct fieldname.
Stefan Schwarzer wrote:
I have an internet map server connected to my database. Until now,
"no data" fields within the table were filled with a "-9999", i.e.
"-9999" equalled "no data available".
Now, for displaying a map with different classes (red for values
from 0-100, green for values from 100-200....) I need to build as
well a class for "no data" (which is displayed in grey). Until now
that worked perfectly well with the "-9999" values. But since I
inserted a couple of new countries (which do not find any
corresponding values in the tables, as they don't yet exist), I
receive the usual "-9999" plus "NULL" values. Both should be
considered as "no data" and thus displayed in grey.
Unfortunately the mapserver can't deal with NULL values. So, I
can't build a class saying
if values = NULL do something
but instead it only works with "fake" NULL values as -9999
if values = -9999 do something
Stef
Aside from your database structure being problematic, what are
you trying to accomplish?
In other words, what do you want to replace the nulls with and in
what circumstance?
I imagine your table looks like this
ID,country,1950,1951,1952,1953,....
1 usa 50 null 70 10
2 canada 10 45 null 4
Please mention what you would like to do with this?
Stefan Schwarzer wrote:
On Thu, Sep 07, 2006 at 07:45:19AM +0200, Stefan Schwarzer wrote:
Hi there,
is there a simple way to replace NULL values in multiple columns
within the SQL statement? I changed the underlaying country
template
of your database; so now there are a couple of NULL values when I
join the stats-table with the country table. Unfortunately, my
queries have always multiple (year) columns, so I can't do a
kind of
manual replace.
I found that the COALESCE command does something like this, but I
couldn't figure out how this works.
Yes, COALESCE replaces NULLs, however your examples have
neither NULLs
nor use COALESCE, so I don't understand what your question is.
Please repost with an actual example of your problem.
As I said, I couldn't figure out how COALESCE would work on
multiple columns (without naming them explicitly).
So, say I have a table with columns for each year between 1970
and 2005. For specific countries the values might be NULL,
depending if the statistical table has been updated recently
(then they will have a value), or not (then they will be NULL).
A sample query would thus be something like:
SELECT * FROM pop_density
---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your
desire to
choose an index scan if your joining column's datatypes do
not
match
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
