🚀 go-pugleaf

On Thu, 15 Jul 1999 13:42:50 +0900, Mike Ryan <mryan@kc4.so-net.ne.jp>
wrote:

>I have a select in which I NEED to convert a NULL to ANY numeric value
>e.g. 0, -1 ... whatever, I dont care, I just need a numeric value.
>
>in the table temp, there is 2 fields a&b , the table contains records
>where b is sometimes NULL, the data type of both fields are INT. There
>is no way I can change the code so that NULL defaults to 0, it has to be
>NULL.
>
>I have tried
>
>select a,cast(b as int) from temp
>
>select a,1+cast(b as int) from temp
>
>select a,length(cast(b as char)) from temp 

Unfortunately, none of the above approaches is going to work in local SQL.
Local SQL follows the usual SQL behavior in that an arithmetic operation
using a numeric value and a NULL value always results in a NULL value. But
unlike in standard SQL, local SQL's subset of SQL-92 does not provide the
means for circumventing this situation: the CASE construct. In standard
SQL, you can give a column one value or another based on a logical
condition using the CASE construct.

In local SQL you can do this in roundabout ways. One approach is based on
the data itself. If you have a numeric column with which you would be
performing arithmetic operations that would be thrown off by NULL values,
ensure that there are never any NULL values in the column. In other words,
provide a default, non-NULL value (typically zero). With local tables
types, do this at the application level. In handlers for such dataset
events as OnNewRecord and BeforePost, you can automatically supply a zero
value for the column if it currently only has a NULL value (determined
using the TField.IsNull property).

In SQL, you would need to handle NULL values and arithmetic operations
using multiple SELECT statements. One statement would retrieve just the
rows where the numeric column is not NULL (using the IS NULL predicate
negated with the NOT operator) and use the actual column's value. The other
statement would retrieve just the rows where the numeric column *is* NULL
and provide a literal value instead of the actual column's value.
Concatenate the two result sets into a single result set using the UNION
join.

For example, where the numeric column named "B" may contain a NULL value:

  SELECT a, 1 + b AS B
  FROM temp
  WHERE (b IS NOT NULL)
  UNION ALL
  SELECT a, 1
  FROM temp
  WHERE (b IS NULL)

>LENGTH seems to be a reserver word, but the query will not work with it.
>It looks like there is no LENGTH function in local SQL (I DONT BELIEVE
>IT!!!!!)

The LENGTH keyword is indeed a reserved word -- as far as the BDE is
concerned. The BDE, after all, has to be able to deal with database systems
other than local database types through local SQL. It is just that local
SQL does not include this function in its subset of SQL-92. In the local
SQL online help, see the topics on reserved words and unsupported syntax.

Local SQL (what the BDE uses for dBASE, Paradox, and FoxPro tables) is
documented in the online help file LOCALSQL.HLP, found in the main BDE
directory. This help file is a language reference of the local SQL
implementation (subset) of SQL-92. The copy of this file that came with BDE
4.x (and earlier versions) was outdated and has since been rewritten.
Updated copies will have the topic "Unsupported language" in the index (and
lack of this topic indicates a pre-update copy). I can e-mail you a copy of
the updated file if you need and desire it.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski         "Television is a medium because anything well done
Felton, CA             is rare."
                                                 -- Fred Allen (1894-1956)