SELECT statement with empty field value

[jknauf]

Hi,

I have problems with ADS (12.2) with a SELECT statement and empty field values:
It works not correctly!
I have already tried different variants:

select * from DEMO where PARNR = 20 AND Empty(TXTFLD)
select * from DEMO where PARNR = 20 AND TXTFLD <> 'X'
select * from DEMO where PARNR = 20 AND TXTFLD = ''

None of this works reliably.
What can I do?

[michael_loop]

I am surprised that any rows returned no value. NULL should have come back as 0. 32, though, is a space, which is not empty nor null in an ADT table. Therefore it is appropriate that TXTFLD <> 'X' should return 11 rows, since ' ' <> 'X'. NULL <> 'X' is indeterminate.

If you run

UPDATE DEMO
SET TXTFLD = null;

You should find that

select * from DEMO where PARNR = 20 AND Empty(TXTFLD);

works

I would expect that

select * from DEMO where PARNR = 20 AND TXTFLD IS NULL;

would also work. Does it currently return 0 rows?

[jknauf]

Obviously this is a bug!

The only solution I found is:
SELECT * FROM demo WHERE PARNR = 20 and ifNull(TxtFld,'') = ''

But this is not very satisfying.

[michael_loop]

You haven't told us yet what happens when you run

SELECT * FROM DEMO WHERE PARNR = 20 AND TXTFLD IS NULL;

except that it "doesn't work" and returns no error.

[jknauf]

>>select * from DEMO where PARNR = 20 AND TXTFLD IS NULL;

Only 11 from 20 hits are listed. So not all records are found.

[michael_loop]

This does sound like incorrect behaviour but I can't reproduce it. If you can provide a sample table that does not contain any sensitive data, I will submit it to our developers for investigation.

[michael_loop]

What happens when you use IS NULL? Do you get an error? If so, what is the error?

What data types are you using? I created a table called DEMO with an integer field called PARNR and a memo field called TXTFLD. I entered 20 rows with no value for TXTFLD.

SELECT * FROM DEMO WHERE PARNR = 20 AND TXTFLD IS NULL;

returned 20 rows.

Are there rows in your table where TXTFLD is not empty? Is 11 the correct number of rows that are both non-empty and not 'X'?

Does your table have a primary key?

If you run

SELECT ASCII(TXTFLD) FROM DEMO WHERE PARNR = 20;

Do all empty rows come back as 0?

[jknauf]

>>What happens when you use IS NULL? Do you get an error? If so, what is the error?
No error!

>>I created a table called DEMO with an integer field called PARNR a
PARNR Integer and TXTFLD CHAR 1

>>SELECT ASCII(TXTFLD) FROM DEMO WHERE PARNR = 20;
All 20 rows appear but 11 rows with the value 32 and 9 rows with no value.

[jknauf]

select * from DEMO where PARNR = 20 AND TXTFLD IS NULL do not work.

We use the .adt file format with the ADsDatarReader.
The table contains 20 rows with unset field values and PARNR = 20 for the field TXTFLD.

select * from DEMO where PARNR = 20 AND Empty(TXTFLD) provides 4 hits
select * from DEMO where PARNR = 20 AND TXTFLD <> 'X' provides 11 hits
select * from DEMO where PARNR = 20 AND TXTFLD = '' provides 0 hits

Juergen Knauf

[jhoehne]

we're using always

TRIM(IFNULL(Fieldname, '')) = ''

for an empty-test. So that NULL, '' and ' ' are all found "empty". Too many problems without this....

[jknauf]

Thank you.

[michael_loop]

Have you tried

select * from DEMO where PARNR = 20 AND TXTFLD IS NULL

?

If you are using ADT or VFP tables and your empty field is truly empty, as opposed to a space or a zero, then the value is NULL. Any test for equality or inequality against NULL will fail. If my phone number and your phone number are both NULL in a database, it does not mean that we have the same phone number. It also doesn't preclude that we have the same phone number. See NULL Values.

The DBF file format treats NULL as equal to 0 or an empty string.

The EMPTY() function should have worked. When I run:

SELECT * FROM custoly WHERE First_Name = 'Marulla' AND Empty(VIP_Status);

against the sample data under \Advantage ??.?\Help\ADS_DATA I get the correct answer.

Can you give more details of your file format, the results you expect, and the results or error you receive?

Mike Loop
Senior Product Support Engineer
SAP Product Support