New post
Question
· Jan 14

Computed/generated/stored columns

#InterSystems IRIS for Health

I have a Postgres table which should be migrated to IRIS. The table has a computed column, like:

CREATE TABLE example_table (
    id VARCHAR(10) PRIMARY KEY,
    normalized_id VARCHAR(10) GENERATED ALWAYS AS (LPAD(id, 10, '0')) STORED
);

IRIS also has the LPAD function but I can't figure out how to achieve the same result using pure SQL DDL.

Tried this:

CREATE TABLE example_table (
    id VARCHAR(10) PRIMARY KEY,
    normalized_id VARCHAR(10) COMPUTECODE {
        set {normalized_id} = LPAD({id}, 10, '0')
    }
    COMPUTEONCHANGE(id))

Got the following error:

SQL Error [400] [S1000]: [SQLCODE: <-400>:<Fatal error occurred>]
[Location: <ServerLoop>]
[%msg: <ERROR #5475: Error compiling routine: SQLUser.exampletable.1.  Errors:  ERROR: SQLUser.exampletable.1.int(1003) #1017: Invalid operator : ''0')' : Offset:31 [znormalizedidCompute+3^SQLUser.exampletable.1]
  > ERROR #5030: An error occurred while compiling class 'SQLUser.exampletable'>]

Changing single quotes to double quotes doesn't work

Product version: IRIS 2025.3
Discussion (7)3
Log in or sign up to continue
DC AI Bot · Jan 14

To address the implementation of a computed column in InterSystems IRIS similar to the PostgreSQL query you provided:

IRIS does not support GENERATED ALWAYS AS directly in SQL DDL commands for computed columns. The approach involves using COMPUTECODE. However, the error you encountered, suggests issues with syntax or implementation. A viable alternative might involve defining the logic in an appropriate trigger function or querying with derived columns directly [1][2].

For further assistance, you might consider asking for specific guidance on the Developer Community, especially if the proposed methods or adaptations still do not align with your requirements.

Sources:

0 0
Vitaliy Serdtsev · Jan 15

LPAD is a SQL function, not an ObjectScript function, which is why it didn't work for you if you replaced the single quotes with double quotes.

USER>write ">"_LPAD(1,10,"0")_"<"
><
USER>write ">"_$$lpad^%qarfunc(1,10,"0")_"<"
>0000000001<

There are two ways to solve the problem: using embedded SQL or an analog of LPAD for ObjectScript, for example:

CREATE TABLE example_table (
    id VARCHAR(10) PRIMARY KEY,
    normalized_id VARCHAR(10) COMPUTECODE {
        &sql(select LPAD(:{id}, 10, '0') into :{*})
    }
    COMPUTEONCHANGE(id))
2 0
Vitaliy Serdtsev · Jan 16

Rather, the discussion was about out-of-the-box equivalents of some SQL functions, so that programmers wouldn't have to reinvent the wheel. Furthermore, the routine of ^%qarfunc is undocumented.

For example, making an Object Script analog of VECTOR_COSINE for integer vectors yourself is a non-trivial task, given that $VECTOROP doesn't support the integer type.

Sometimes it's easier to use embedded SQL.

1 0