NVL2 alternative
Jonathan Taylor, November 01, 2018 - 1:36 pm UTC
Alternatively:-
NVL2(text,1,0) + NVL2(adr,1,0) = 1
This is a shorter (but less readable) way of doing this.
However, it is a lot shorter if the number of columns is 3, 4 or more.
SQL> CREATE TABLE TEST (
2 ID NUMBER(9,0),
3 TEXT VARCHAR2(7),
4 ADR VARCHAR2(4),
5 CONSTRAINT L_COL_CHK
6 CHECK (
7 NVL2(text,1,0) + NVL2(adr,1,0) = 1
8 )
9 ) ;
Table created
SQL> INSERT INTO test VALUES ( 1,'er','nzb' );
INSERT INTO test VALUES ( 1,'er','nzb' )
ORA-02290: check constraint (FN.L_COL_CHK) violated
SQL> INSERT INTO test VALUES ( 2,'er',NULL );
1 row inserted
SQL> INSERT INTO test VALUES ( 3,NULL,'nzb' );
1 row inserted
SQL> INSERT INTO test VALUES ( 4,NULL,NULL );
INSERT INTO test VALUES ( 4,NULL,NULL )
ORA-02290: check constraint (FN.L_COL_CHK) violated
SQL>
November 02, 2018 - 4:56 pm UTC
True, though I generally prefer readability
raj v, November 05, 2018 - 9:30 pm UTC
Thank you soo much TOM, I feel any of the check constraint or NVL function can be applied to my requirement.
You are awesome!
November 06, 2018 - 2:14 am UTC
glad we could help