Creating table using UCASE

[sanbdbrn]

In DB2 V7 (mainframe) can I create a table that checks
a column for upper case (UCASE)? The manual indicates I cannot and I am not having any success. If there is a way, how would it be coded? Here is what I'm using....
CREATE TABLE DHP0000.MTHP0290_278_PHNUM
P278_SYS_CD CHAR(10) NOT NULL
CHECK (P278_SYS_CD IS UCASE(P278_CNTCT_CD))
It's not working.
Thanks for any help!

 

[Mercury2]

Hmm...

Try out this tricky code :

CREATE TABLE DHP0000.MTHP0290_278_PHNUM                  
    P278_SYS_CD CHAR(10) NOT NULL                 
     CHECK [b](HEX([COLOR=red]XOR[/color](UCASE(P278_SYS_CD), P278_SYS_CD))[/b] = '00000000000000000000') /* as many 00 as field length */

Then try to insert in lowercase and mixed case in field P278_SYS_CD. You should get a message stating that "INSERT or UPDATE not allowed by CHECK constraint."
That should work when inserting or updating all in upper case.

 

[sanbdbrn]

Thank you, Mercury.
I tried it but was unsuccessful. Is XOR perhaps a routine or stored procedure someone wrote in your shop? I'm getting
-440 NO routine-type BY THE NAME routine-name HAVING COMPATIBLE ARGUMENTS WAS FOUND

Explanation: This occurs in a reference to routine (stored procedure or
function) routine-name, when DB2 cannot find a function or stored
procedure it can use to implement the reference. There are several reasons
why this could occur......
(I didn't record the entire message here).

Thanks again!!!

 

[Mercury2]

sanbdbrn ,

The XOR SQL function returns a string that is the logical XOR of the argument strings aka exclusive OR and can't be in no way a routine or whatever that someone wrote in my shop.
Can you try to carry out the Exclusive OR equivalence on your DB2 stm ?

 

[sanbdbrn]

Thanks again, Mercury......
sorry....but you lost me.