I understand that EDB treats Null's differently from DBISAM.

So

WHERE NOT Name = Null

needs to be rewritten:

WHERE Name IS NOT Null

However, I am running queries in this form & NULL records are still appearing. At least they show as "null in the EDBManager.

Why would this be?

Here is the script I am running

SELECT
 O.Name+' '+
 C.Title+' ('+
 C.WhoToList+' '+
 C.WhoFrom +')' as Name,
 C.ID

FROM
   Communications C
   LEFT JOIN Organisations O ON (C.OrganisationsID=O.ID)

WHERE DateSent >= Current_Date - INTERVAL '120' DAY
AND Name > ''    
AND Name IS NOT Null
ORDER BY Name

Adam,

<<
- You can now specify whether you want ANSI-SQL standard NULL behavior (the
default), or non-ANSI-SQL NULL behavior where NULLs are comparable with
normal operators (=,<>,>,<,BETWEEN, etc.) and NULL values are treated like
an empty version of the containing value's data type. For example, with
non-ANSI-SQL NULL behavior enabled, the following SELECT statement will
return all rows where the State column is NULL:

SELECT * FROM Customer
WHERE State=NULL
This feature does not affect anything other than SQL comparisons, and does
not affect how required (NOT NULL) columns work, how the IS NULL/IS NOT
NULL
operators work, or how primary, unique, and foreign key constraints work
with respect to NULLs.

The TEDBEngine StandardNullBehavior property controls this setting for the
entire engine, and the TEDBSession LocalStandardNullBehavior property
controls this setting for specific sessions when the TEDBEngine
UseLocalSessionEngineSettings property is set to True.
>>

Here the link of the complete article:

https://forums.embarcadero.com/message.jspa?messageID=274353

Regards Uli

Adam

This is a guess but I think you've fallen foul of the all new (and I hate it) NULL behaviour. I also think you're confusing yourself with your use of Name.

What you're seeing in EDBManager is   O.Name+' '+  C.Title+' ('+  C.WhoToList+' '++  C.WhoFrom +')'

What you're testing for (I assume) is O.Name

My guess is that for the rows where you're seeing NULLs is that one or more of the columns used to generate Name is NULL. The new behaviour is that anything added to NULL becomes NULL (and I repeat I hate it).

What you'll need to do is COALESCE the columns to ensure an empty string is returned eg

SELECT
 COALESCE(O.Name,'')+' '+
 COALESCE(C.Title,'')+' ('+
 COALESCE(C.WhoToList,'')+' '+
 COALESCE(C.WhoFrom,'') +')' as Name,
 C.ID

FROM
   Communications C
   LEFT JOIN Organisations O ON (C.OrganisationsID=O.ID)

WHERE DateSent >= Current_Date - INTERVAL '120' DAY
AND COALESCE(Name,'') > ''   ..... no need to do this but I thought "what the hell"
ORDER BY Name


Personally I would leave the engine set as ANSI compliant - you might as well get used to it.

Final point - be careful about calling simple sql statements scripts and ElevateDB script is a totally different beastie and its a good idea to get your mindset oriented to it.

Roy Lambert [Team Elevate]

Thanks Uli

I saw this article & I do understand the behaviour.

However the practical example I gave shows something different.

i.e.

Query in the form

WHERE Name IS NOT Null

is still returning rows which _are_ Null.

This seems like "wrong" behaviour to me.

Thanks Roy ... I am sure this is the problem.

My

WHERE Name IS NOT Null

needs to include checks on all the other columns. Stupid of me.

Also I will try to get my head around the difference between a SQL Statement and a SQL Script (

Adam,

> Thanks Uli
> I saw this article & I do understand the behaviour.
> However the practical example I gave shows something different.

Sorry - I should have read your post more accurately. I just read:

<<
WHERE NOT Name = Null
needs to be rewritten:
WHERE Name IS NOT Null
>>

Regards Uli

Adam

>WHERE Name IS NOT Null
>
>needs to include checks on all the other columns. Stupid of me.

I'm sure there are cases where you have the name but not the title etc so unless you're saying you don't want the row if ANY of the columns that are merge to make Name are NULL don't do that. Leave the test as is and use COALESCE as per my example.


Roy Lambert [Team Elevate]