null indication in number field

[ranger]

would like to indicate when field was not writen to OR if a null value was
posted in a number field. is there any way to write 'null' or other marker to a
field set up to record a single number. can't use 0 as throws off 'SELECT
avg()' when doing query as n/a entries must be ignored in stats. used in
multiple choice where 'not applicable' is '' visitor can return to page so
needs to post to display selections already made. client does not want a
default choice preselected. tnx in advance.

[kim il sung]

for ms sql server
for statistics
avg works only with numeric values, by default null values are ingnored.
select avg(salary) from foobar where salary IS NOT NULL is same than select
avg(salary) from foobar

posting preselections
you could change null values to some number like 666666 and trap them in your
app.

with text fields you can replace null values with following code:
select ISNULL(salary_text,not applicable') as salary

you can change datatype with CAST and CONVERT functions.

check book on line or google to learn more.
kim

[ranger]

is there any way in SELECT to ignore 0 in AVG tag as in AVG(s1) AS a1 s1 has
to be numeric (can't do in WHERE statement as using a loop in SELECT statement
SELECT <cfloop index=catz from=1 to=96 step=1> AVG(s#catz#) AS a#catz#,
</cfloop> )