Sample Header Ad - 728x90

Why cannot I create a generated column with a CASE expression?

0 votes
1 answer
153 views
I'm trying to add a generated column to a table in V7R3 of IBM DB2:
ALTER TABLE MYLIB.MYTABLE
    ADD COLUMN NEW_COL VARCHAR(255) GENERATED ALWAYS AS (
        COALESCE(
            CASE
                WHEN ENV = 'A' THEN (SELECT GUIDE_URL FROM MYLIBA.PROGRAM WHERE CODE = PGM)
                WHEN ENV = 'B' THEN (SELECT GUIDE_URL FROM MYLIBB.PROGRAM WHERE CODE = PGM)
                ELSE (SELECT GUIDE_URL FROM MYLIBC.PROGRAM WHERE CODE = PGM)
            END
            , URL
        )
    )
But it's giving the following error: >  SQL State: 42601 Vendor Code: -199 Message: [SQL0199] Keyword WHEN not expected. Valid tokens: . ACCTNG USERID APPLNAME PROGRAMID WRKSTNNAME. Cause . . . . . :   The keyword WHEN was not expected here.  A syntax error was detected at keyword WHEN.  The partial list of valid tokens is . ACCTNG USERID APPLNAME PROGRAMID WRKSTNNAME. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again. Am I doing something wrong? Or is it just impossible to create such a generated column in 7.3? Would upgrading to 7.5 help? I tried to find specifics on what generated columns can be made in what versions, but couldn't find anything in the docs.
Asked by Sarov (281 rep)
Sep 6, 2023, 08:18 PM
Last activity: Sep 6, 2023, 08:31 PM