RE: SQL scroll cursor slow performance

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

Another issue comes to mind as I watch Darren slowly work towards a
solution.  Take a look at the fragment of his SQL I copied below:

>      C+        ifnull(min(FKENGD),' ') as EDSC,
>      C+        ifnull(min(FKSPND),' ') as SDSC,
>      C+        ifnull(min(F5COO),' ') as F5COO,
>      C+        ifnull(min(F2TOUM),' ') as F2TOUM,
>      C+        ifnull(min(HARPMT),' ') as HARPMT,
>      C+        ifnull(min(F5SCST),0), ifnull(min(F2FACT),1),

Note all the IFNULLs?  I know you can declare a column that doesn't
allow NULL values (NOT NULL CAPABLE).  It would seem to me that, except
in very rare circumstances, you would declare fields this way and use an
old-fashioned "no value" value.  That would avoid a lot of the IFNULL
claptrap you see in SQL code.

A second option would be to be able to set a default value for each
column and then have a runtime environment setting that allows you to
use the default value whenever you encounter a NULL.  You'd use this for
reporting purposes, for example, when you don't really care about the
NULL value. Note that this is different than a default value and NOT
NULL CAPABLE -- in this scenario you can still set a NULL value and test
for it in programs, but in queries for reporting purposes you would
automagically get the default value, thus saving a step for displays.

I guess it's a little more complicated when the NULLs come from missing
records in JOINs.  If you have a default value for a NULL-capable column
in a file you are JOINing to and the record does not exist, do you get
the default value or NULL?

Joe