Computed/Calculated columns in CREATE TABLE should be able to also reference non-key columns
It is not currently possible in Octo to access non-key columns in calculated values defined in DDL statements. Example from the test system:
CREATE TABLE namesWithAges (
id INTEGER PRIMARY KEY, firstName VARCHAR(30), lastName VARCHAR(30), age INTEGER,
datetime TIME(10) EXTRACT "$ZDATE($HOROLOG,""YEAR-MM-DD 24:60:SS"")" -- Calculated, but no variables
) GLOBAL "^names(keys(""id""))";
In other words, you can make a fully calculated field, but not a calculated field based on other, non-key columns within the table. For example:
CREATE TABLE xxxx (
id INTEGER PRIMARY KEY, firstName VARCHAR(30), lastName VARCHAR(30), age INTEGER,
xxxxxxx VARCHAR EXTRACT "$$MY^FUNC(keys(""id""),firstName,lastName)" #<<<===
) GLOBAL "^xxxxx(keys(""id""))";
This query attempts to access the firstName
and lastName
columns within the M code specified by EXTRACT
. However, this will not work because these column names are not actually M variables. For example:
; select * from xxxx;
;; ---------------------------------------------------------
octoPlan0(cursorId)
DO octoPlan1(cursorId)
QUIT
octoPlan1(cursorId)
NEW %ydboctoz KILL %ydboctocursor(cursorId,"keys",3,"","")
SET %ydboctocursor(cursorId,"keys",1,"XXXX","ID")=""
FOR SET %ydboctocursor(cursorId,"keys",1,"XXXX","ID")=$ORDER(^xxxxx(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"))) QUIT:(%ydboctocursor(cursorId,"keys",1,"XXXX","ID")="") DO
. NEW %ydboctobool SET %ydboctobool=1
. IF %ydboctobool DO
. . SET:1&$INCREMENT(%ydboctocursor(cursorId,"keys",3,"","")) %ydboctoz=42
. . SET %ydboctocursor(cursorId,"keys",3,"","",%ydboctocursor(cursorId,"keys",3,"",""))
=$$str2mval^%ydboctoplanhelpers(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"))
_$$str2mval^%ydboctoplanhelpers($$empty2null^%ydboctoplanhelpers(0,-1,"VARCHAR",$PIECE($GET(^xxxxx(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"))),"|",1)))
_$$str2mval^%ydboctoplanhelpers($$empty2null^%ydboctoplanhelpers(0,-1,"VARCHAR",$PIECE($GET(^xxxxx(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"))),"|",2)))
_$$str2mval^%ydboctoplanhelpers($$empty2null^%ydboctoplanhelpers(0,-1,"INTEGER",$PIECE($GET(^xxxxx(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"))),"|",3)))
_$$str2mval^%ydboctoplanhelpers(
$$empty2null^%ydboctoplanhelpers(0,-1,"VARCHAR",
$$MY^FUNC(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"),firstName,lastName)))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
QUIT
In this case, firstName
and lastName
are undefined M variables, since they are simply passed through directly from the EXTRACT
specification without modification.
Instead, these names from the EXTRACT
statement should be replaced with valid M code. For example:
$PIECE($GET(^xxxxx(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"))),"|",2) ; firstName
$PIECE($GET(^xxxxx(%ydboctocursor(cursorId,"keys",1,"XXXX","ID"))),"|",3) ; lastName
To allow this mapping to be established at DDL creation, a values()
field may be specified in the EXTRACT
specification. For example:
CREATE TABLE xxxx (
id INTEGER PRIMARY KEY, firstName VARCHAR(30), lastName VARCHAR(30), age INTEGER,
xxxxxxx VARCHAR EXTRACT "$$MY^FUNC(keys(""id""),values(""firstName""),values(""lastName""))"
) GLOBAL "^xxxxx(keys(""id""))";
Then, during DDL creation, these parameters should can be replaced by values representing the columns named within each values()
call.