I've got a table with the following:
Columns: --- URL [nullable, e.g. "https://dba.stackexchange.com/"] APPFK [nullable, e.g. 654654] LINKTYPE ["Page", "Link", or "App"] Check Constraints: --- (URL IS NULL AND LINKTYPE <> "Link") OR (URL IS NOT NULL AND LINKTYPE = "Link") (APPFK IS NULL AND LINKTYPE <> "App") OR (APPFK IS NOT NULL AND LINKTYPE = "App")
Since LinkType is calculable based on the nullability of URL and APPFK, I figured this was a perfect opportunity for a computed/generated/virtual column.
So, I tried to run:
alter table MYLIB.MYTABLE add column testcol VARCHAR(4) generated always as ( CASE WHEN URL IS NOT NULL THEN 'LINK' WHEN APPFK IS NOT NULL THEN 'APP' ELSE 'PAGE' END )
Annnd DB2 spat this back at me:
SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token ( was not valid. Valid tokens: IDENTITY. Cause . . . . . : A syntax error was detected at token (. Token ( is not a valid token. A partial list of valid tokens is IDENTITY. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token (. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.
I tried running the first example in this DB2 documentation, but it gave me the same error.
What's going wrong, here? Am I doing something wrong? Is the documentation wrong? Is it just because our DB2 is outdated (we're running Version 7, Revision 1), and if so is there a workaround?