Again, just upgrading from DBISAM - here's my query :-

----------------------------
select report.id, report.name, report.description, report.reporttype,
report.reportcat_id,
report.createdon, report.updatedon,

(case report.reportcat_id when NULL then '<No Category>' else reportcat.name
end) as reportcatname,
cr_staff.knownas as createdby,
up_staff.knownas as updatedby

from report
left join reportcat on (report.reportcat_id = reportcat.id)
left join staff as cr_staff on (report.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (report.updatedby_staff_id = up_staff.id)

order by report.name
----------------------------

The error is :-
ElevateDB Error #700 An error was found in the statement at line 4 and
column 32 (Expected Boolean, SmallInt, Integer, BigInt, Float, Decimal,
Interval Year, Interval Day, Interval Hour, Interval Minute, Interval
Second, or Interval MSecond expression but instead found NULL)

This SQL works fine in DBISAM and PGSQL - is it non-standard (in which case,
what should it be?) or can this be fixed in ElevateDB?

Many thanks.

Hedley

>(case report.reportcat_id when NULL then '<No Category>' else reportcat.name

Remember that with ElevateDB NULL has been elevated (nice pun which I didn't think of in advance) to a special state. It no longer equals EmptyString and can only be operated on using its own special syntax.

I don't think you can use COALESCE here so you'll need an IF or IFNULL eg

IFNULL( report.reportcat_id then '<No Category>' else reportcat.name) as reportcatname,

or

IF(report.reportcat_id IS NULL then '<No Category>' else reportcat.name
end) as reportcatname,

BTW I'm on the heretics side of the argument about NULL and EmptyString

Roy Lambert [Team Elevate]

Hi Roy,

>> Remember that with ElevateDB NULL ... no longer equals EmptyString
>> and can only be operated on using its own special syntax.

I always thought that was pretty much the standard? SQL Server, PostgreSQL,
MySQL and VistaDB all treat String.Empty != Null. DBISAM and Oracle are the
only ones I've come across where String.Empty == Null.

As Tim is trying to make ElevateDB more 'standard' than DBISAM, I think the
change makes sense (although I'm sensing you don't agree!).

Anyway, regarding the original question, I don't think the changed status of
NULLs in EDB is what's causing the problem with the CASE statement. CASE
statements have two possible syntaxes :-

Syntax 1

CASE expression
   WHEN value THEN result
   [WHEN ...]
   [ELSE result]
END

Syntax 2

CASE WHEN condition THEN result
    [WHEN ...]
    [ELSE result]
END

In my query I was using Syntax 1 i.e.

case report.reportcat_id
 when NULL then '<No Category>'
 else reportcat.name
end as reportcatname,

This works happily under DBISAM and PostgreSQL (which treats NULLs !=
String.Empty) but it fails with EDB.

I just tried changing it to syntax 2 i.e.

case when report.reportcat_id is NULL
   then '<No Category>'
   else reportcat.name
end as reportcatname,

.... and it works fine with EDB!

So I have a work-around, but this might still be something for Tim to
investigate?

Hedley


>As Tim is trying to make ElevateDB more 'standard' than DBISAM, I think the
>change makes sense (although I'm sensing you don't agree!).

Correct and I thinks Tim's change makes sense from his business viewpoint - its the standard I don't think makes sense - I'm a heretic

>case when report.reportcat_id is NULL

Could be wrong, but my view is you are now using NULL's special syntax whereas before you weren't.

Roy Lambert [Team Elevate]

Hedley,

<< The error is :-
ElevateDB Error #700 An error was found in the statement at line 4 and
column 32 (Expected Boolean, SmallInt, Integer, BigInt, Float, Decimal,
Interval Year, Interval Day, Interval Hour, Interval Minute, Interval
Second, or Interval MSecond expression but instead found NULL)

This SQL works fine in DBISAM and PGSQL - is it non-standard (in which
case, what should it be?) or can this be fixed in ElevateDB? >>

I'll have to double-check on this with the SQL 2003 standard.  Usually,
NULLs are always a special case with comparisons in SQL, i.e. you cannot use
them like a normal value since they are the absence of a value and require
the special IS [NOT] NULL comparison on the source column reference to
determine if such a reference is NULL or not.  IOW, what you're asking
ElevateDB to do is essentially this:

IF report.reportcat_id=NULL THEN

and that's a no-no in SQL.  A NULL constant cannot be compared directly
against another value.

For now, the workaround is to use the alternative CASE syntax with the full
expressions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley,

It appears that it may be for the best that we don't allow this in
ElevateDB, since the result will never be what you think it will be.  See
here:

http://en.wikipedia.org/wiki/Null_(SQL)

under "CASE expressions".

Since NULL <> NULL, you would never get a match on that CASE WHEN NULL
branch anyways.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I especially like

"Chris Date and Hugh Darwen, authors of The Third Manifesto, have suggested that the SQL Null implementation is inherently flawed and should be eliminated altogether"

Roy Lambert