Table creation and inserting values on not null filed

[NipunJain]

I have created a table and below is the table defination:

CREATE SET TABLE test_db_devl.cvs_provider ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CVS_PRV_ID INTEGER NOT NULL,
APPL_PV_CNTRT_AFF_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
APPL_PV_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)
UNIQUE PRIMARY INDEX CVS_PRV_i01 ( CVS_PRV_ID );

Now I have inserted values into table :

INSERT INTO test_db_devl.cvs_provider 
(CVS_PRV_ID, APPL_PV_CNTRT_AFF_NBR, APPL_PV_NBR)
VALUES ('1','','');

Now below two fields have single quotes:

APPL_PV_CNTRT_AFF_NBR,

APPL_PV_NBR

But there values are defiend as not null so what is the impact of that.

Fields are defined as not null and quotes work as null.

Can someone explain this scenario.

[dnoeth]

---

quotes work as null.

Nope, only when you used to work with Oracle :-)

An empty string is a value which is different from a NULL in every DBMS supporting Stnadard SQL.

[NipunJain]

Hi,

I have craeted the table in teradata and table accept '' value.

So i am curious to know the difference between that not null field and field which is defiend as null.

[dnoeth · Accepted solution]

An empty string '' is not the same a NULL.

where APPL_PV_NBR = '' only returns a row with an empty string

where APPL_PV_NBR IS NULL only returns a row with a NULL

Just try it

[NipunJain]

Thank You very Much, now i got it.