Null accepted in column defined as Not null

Database
Enthusiast

Null accepted in column defined as Not null

When I actually get the HEXINT value for that record on that column, it was returning ‘00000000000000000000’. Now actually we were able to get the hex character loaded in that particular column and it is “x’00’”. This Hex null  is somehow loaded into Teradata table through datastage jobs. 

DDL:
VND_AP_NUM CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

SEL vnd_ap_num FROM vendor.vnd
WHERE
vnd_num = 1548 and vnd_ap_num is null;
Result 0

sel chars(trim(vnd_ap_num)) from vendor.vnd where vnd_num = 1548;
Result 12

SEL chars(trim(oreplace(vnd_ap_num,null,''))) FROM vendor.vnd
WHERE
vnd_num = 1548;
Result 0

Why would multiple null be accepted in a Not null column?

Rglass

1 REPLY
Junior Supporter

Re: Null accepted in column defined as Not null

Hi:

'00'xC is not a Teradata NULL. You can insert '00'xC in a NOT NULL CHAR column.

oReplace is returning white spaces for the '00'xC (it does nor replace them at all) and the trim removes them. That's why you see 0 as result.

BTW: I don't think you can use NULL as a parameter in oReplace with the results that you expect.

HTH.

Cheers.

Carlos.