RE: INSERT & ZIP

[email protected] wrote

> I've created a table with the following script:
> 
> 	CREATE TABLE customer  
> 	(  
> 		 cno        FIXED(12,0) DEFAULT SERIAL PRIMARY KEY,  
> 		 title      CHAR(5),  
> 		 name       CHAR(10) NOT NULL,  
> 		 firstname  CHAR(7),  
> 		 zip        CHAR(5) CONSTRAINT zip like 
> '(0-9)(0-9)(0-9)(0-9)(0-9)',  
> 		 city       CHAR(12) NOT NULL  
> 	)
>  
> Now I want to add a few datas into the table:
> 
> 	insert into customer (title, name, firstname, zip, city) values
> ('Prof.','Mayer', 'Sepp', '12345', 'Erbsenstadt')
> 
> By executing this statement I just get the following error message:
> 
> 	General error;300 POS(1) Integrity violation:ZIP,CUSTOMER,DBA.  
> 	insert into customer (title, name, firstname, zip, city) values
> ('Prof.','Mayer', 'Sepp', '12345', 'Erbsenstadt') 
> 	
> My question: which is the right format for ZIP?

With version 7.4.3.17 we changed the like behaviour of SAP DB 
to be more standard compliant.
With this change we removed the special handling of '*', '?'
and also the possibility to define character ranges like '(0-9)'.

So if you use a version >= 7.4.3.17 you have to change your 
constraint within the table definition to

CONSTRAINT
  SUBSTR(zip,1,1) between '0' AND '9' AND
  SUBSTR(zip,2,1) between '0' AND '9' AND
  SUBSTR(zip,3,1) between '0' AND '9' AND
  SUBSTR(zip,4,1) between '0' AND '9' AND
  SUBSTR(zip,5,1) between '0' AND '9'

Sorry for any inconvenience.

Best regards,
Holger