Looking for an option to handle null-values

[invent]

Hi,

perhaps this question been asked a hundred times. In this case, I apologize that I ask it again:

Is there any option in TUniConnection or in TUniQuery to include null-values?

Example:

select * from mytable where myfield 'XYZ'

shows my only rows which not empty myfields 'XYZ'.

So I have

500 for select count(*) from mytable
100 for select count(*) from mytable where myfield = 'XYZ'
200 for select count(*) from mytable where myfield 'XYZ'
and the rest have myfield = null.

IMHO is null 'XYZ'.

Thanks for any idea.

Kind regards,
Gerd Brinkmann
invent GmbH

[vallemanden]

uniquery1.Close;
uniquery1.SQL.Clear;
uniquery1.SQL.BeginUpdate;
uniquery1.SQL.Add('SELECT * FROM ordre');
uniquery1.SQL.Add('WHERE moms IS NULL');
uniquery1.SQL.EndUpdate;
uniquery1.open;

will give you all fields where moms is NULL

[AndreyZ]

You should use the following SQL code:

Code: Select all

select * from mytable where myfield  'XYZ' or myfield is null

[invent]

Hi,

thanks for the answers. But that is not the point, I know how to use null is sql. I think that the following codes must have the same result:

Code: Select all

select * from mytable where myfield  'XYZ' or myfield is null

Code: Select all

select * from mytable where myfield  'XYZ'

The Problem is, that with Borland BDE the second code works like the first.

Now I have some customers who bought an update of my software after years and than they have different results to the old version. So I must investigate thousands of queries and I'm looking for a quick solution.

Kind regards,
Gerd Brinkmann
invent GmbH

[AndreyZ]

Please specify the database server (MySQL, SQL Server, etc) you are working with.

[invent]

Hello,

we are using UniDAC 3.50.0.13 for Delphi 7 with Interbase XE, Oracle 8.0.5 and MS SQL-Server 2008 RC2.

But it's not a specific Database-Problem.

Kind regards,
Gerd Brinkmann
invent GmbH

[AndreyZ]

I've checked this question with BDE. With the following SQL code:

Code: Select all

select * from mytable where myfield  'XYZ'

BDE doesn't return NULL values for any database server. I've also checked Microsoft SQL Server Management Studio, FlameRobin, IBExpert, Oracle SQL Developer database tools. They all behave in the same way - NULL values are selected only when the following code is used:

Code: Select all

select * from mytable where myfield  'XYZ' or myfield is null