Prev: Column not getting updated
Next: Truncation at sixth decimal
From: --CELKO-- on 21 Apr 2010 11:40
>> ISNULL allows me to either return the data in a field [sic] or some specified value if the field [sic] is NULL. <<

Columns are not fields -- you are still thinking in file system terms
and not RDBMS. And ISNULL() is a proprietary function that you should
not be using now that we have COALESCE(). And it takes an expression
as its argument.

>> What I want to do is slightly different. I want to return 1 or a 0 based on whether or not there's data in the field [sic: expression]. In other words, I don't want to return the data but a value of 1 if there is data. If there's no data, I want to return a 0. <<

I hate to tell you this, but a NULL is data. Since a column is always
present in a row, what you asked for is a constant function that
returns One. If you were talking about a file system, where the
records do not have to have the same structure, then there could be no
data for a field.

You are a few steps behind the rest of the class and need to do some
more reading, so you get the concepts and terms correct.

I think what you meant to say is that you want a characteristic
function for non-NULLs. One way is:

CASE WHEN <expression> IS NOT NULL THEN 1 ELSE 0 END

You are probably comfortable with that solution because it looks like
procedural code. But you can also try this one:

COALESCE (NULLIF (<exp>, <exp>), 1), 0)

The NULLIF is defined as:
CASE WHEN <exp> = <exp> THEN NULL ELSE <exp> END

This is a functional programming style that is now replacing the OO
model. Get a quick tutorial on F#, Erlang or Haskell for a look at
that family of languages.
From: Kalle Olavi Niemitalo on 21 Apr 2010 18:21
--CELKO-- <jcelko212(a)earthlink.net> writes:

> But you can also try this one:
>
> COALESCE (NULLIF (<exp>, <exp>), 1), 0)

That has two opening and three closing parentheses. Something is
clearly missing from the expression, but I don't understand what.
It seems to me that NULLIF (<exp>, <exp>) is always NULL so there
would be no reason to use that. What did you originally mean?

If <exp> is a number, then one could perhaps use

COALESCE (<exp> * 0 + 1, 0)

which is shorter than the obvious CASE expression but harder to
understand and leaks the type of <exp> to the result. I think
it's about as bad as the a^=b;b^=a;a^=b; swap hack in C.
From: Tony Rogerson on 22 Apr 2010 03:19
> Columns are not fields -- you are still thinking in file system terms
> and not RDBMS.

You are using the term RDBMS, in a Relational Database Management System we
use Attributes and Tuples.

In Relational terms Attributes are not columns, if you are going to persist
in people using the interchangeable term "field" in this context then at
least correct your "bash" and state that the Relational "attribute" is named
"column" in standard SQL - that has nothing to do with RDBMS, there are
other "real" relational database systems that correctly use the terms
Attributes and Tuples.

--ROGGIE--

From: --CELKO-- on 22 Apr 2010 12:48
>> That has two opening and three closing parentheses.  Something is clearly missing from the expression, but I don't understand what. <<

Sorry about that:

COALESCE (NULLIF (<exp>, <exp>, 1), 0)

>> It seems to me that NULLIF (<exp>, <exp>) is always NULL so there would be no reason to use that. <<


This is one of the CASE expression family. It is defined as

CASE WHEN <exp1> = <exp2> THEN NULL ELSE <exp1> END

The data type of the result is the same as <exp1> and the two
expressions have to be comparable.

>> If <exp> is a number, then one could perhaps use
  COALESCE (<exp> * 0 + 1, 0)
which is shorter than the obvious CASE expression but harder to
understand and leaks the type of <exp> to the result. <<

... and it does not work with temporal, approximate numeric and string
expressions. But it is probably pretty fast.

>>  I think it's about as bad as the a^=b;b^=a;a^=b; swap hack in C.. <<

LOL! Do you remember Algol 60? the way assignment was defined, you
could not write a swap in one statement. So when JOVIAL came along,
the inventor added the :=: swap to his language. It mapped into one
assembly language instruction on some computer at the time.

Ghod, I am gettign old!

From: --CELKO-- on 22 Apr 2010 12:53
>> You are using the term RDBMS, in a Relational Database Management System we use Attributes and Tuples. <<

No, attribute and tuple are from the relational model or relational
data base theory. Date has a lot to say on (tuple/row/record),
(attribute/column/field) and (relation and relvar/table/file)
differences.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Column not getting updated
Next: Truncation at sixth decimal