How to factor in null value in a not equal statement in TERADATA

Database
Junior Supporter

How to factor in null value in a not equal statement in TERADATA

Hi,

 

I'm trying to factor in the null value (char) when writting a not equal statement in TERADATA.

For example, in the folllowing code:

 

CREATE TABLE "TEST" AS

(SELECT *

 FROM "TEMP"

WHERE TESTFLG <> '1')WITH DATA;

 

I notice that null TESTFLG (which are char value) are also not being pull and only the TESTFLG value='0' is getting pulled. Anyway I can get null value along with value='0' to be pulled.


Accepted Solutions
Teradata Employee

Re: How to factor in null value in a not equal statement in TERADATA

You could say: WHERE Coalesce(TESTFLG,' ') <> '1'

The result of this Coalesce function is TESTFLG, unless TESTFLG is null, in which case it is a space. Doesn't have to be space - an empty string ('') or anything other than '1' should work.

1 ACCEPTED SOLUTION
3 REPLIES 3
Teradata Employee

Re: How to factor in null value in a not equal statement in TERADATA

You could say: WHERE Coalesce(TESTFLG,' ') <> '1'

The result of this Coalesce function is TESTFLG, unless TESTFLG is null, in which case it is a space. Doesn't have to be space - an empty string ('') or anything other than '1' should work.

Teradata Employee

Re: How to factor in null value in a not equal statement in TERADATA

WHERE (TESTFLG <> '1' OR TESTFLG IS NULL)

Ambassador

Re: How to factor in null value in a not equal statement in TERADATA

Of course you can also use an explicit

WHERE TESTFLG <> '1' OR TESTFLG IS NULL

For more complex queries this will allow the optimizer to apply existing statistics, COALESCE will result in no confidence, possibly resulting in a less good plan.