New post
AllAccepted answers
Vitaliy Serdtsev · Apr 27, 2022

If the data already exists, then this is a non-trivial task, especially if inheritance or Parent/Child is present, since this will lead to a change in the storage scheme of your data.

The easiest way to do this is through an intermediate (temporary) table:

  1. create a new class with the same structure, but with a new primary key;
  2. move data from the old class into it using SQL (not the merge command);
  3. delete data/indexes in the old class, then change the primary key in it;
  4. move data from the new class to the old class, using the merge command;
  5. delete the new class with the data;
  6. rebuild the indexes if there are any.

Useful links:
MERGE
Persistent Objects and InterSystems IRIS SQL
Introduction to Persistent Objects

If you feel insecure with Caché/IRIS, it is better to ask WRC for help.

Vitaliy Serdtsev · Apr 27, 2022

From doc:

AVG returns either NUMERIC data type values or DOUBLE data type values. If expression is data type DOUBLE, AVG returns DOUBLE; otherwise, it returns NUMERIC.

For non-DOUBLE expression values, AVG returns a double-precision floating point number.proof

The specification of the field [%Time(FORMAT=1)] plays absolutely no role in the case of an aggregate function, since AVG returns just a number (DOUBLE or NUMERIC).

Above I gave links to examples of how a number can be converted to TIME, then to STRING.

For example:

  1. if in the current locale TimeFormat = 1
    select %external(CAST(+166.38 as TIME))

    2. Important: it is necessary to convert a float number to an integer type, otherwise you will get zero.

  2. if in the current locale TimeFormat <> 1
    select TO_CHAR(166.38,'HH24:MI:SS')
Vitaliy Serdtsev · Apr 26, 2022

It is very strange that the mechanism ^%SYS("CSP", "MimeFileClassify") does not work for you.

According to the sources of %CSP.StreamServer, it can be seen that all the work takes place in the FileClassify method, where ^%SYS("CSP", "MimeFileClassify") is also used.

I used ZEN on Caché and all was fine.

Can you give a simple CSP example to reproduce your situation?

Vitaliy Serdtsev · Apr 26, 2022

Try this (%Dialect):

Set sqlStatement=##class(%SQL.Statement).%New(,,"MSSQL")
 
Simple example
Vitaliy Serdtsev · Apr 13, 2022

What locale are you using? I use "rusw".

Try to do the following in SMP:

update MSDS_Serenity.KitlabelAssoc set PackingTimeSpent=35000 where label='00007IT4'

then

select

  PackingTimeSpent,
  %internal(PackingTimeSpent) ptsLOGICAL,
  %external(PackingTimeSpent) ptsDISPLAY,
  %odbcout(PackingTimeSpent)  ptsODBC

from MSDS_Serenity.KitlabelAssoc where label='00007IT4'

What are your results?

I have the following displayed (for DISPLAY mode):

PackingTimeSpent = 09:43:20
ptsLOGICAL = 35000
ptsDISPLAY = 09:43:20
ptsODBC = 09:43:20
Vitaliy Serdtsev · Apr 13, 2022
Notes on Indices Defined in Classes 
When working with indices in class definitions, here are some points to keep in mind:

• Index definitions are only inherited from the primary (first) superclass.
• ...
 
Simple sample
Vitaliy Serdtsev · Apr 13, 2022
having the field defined as integer would allow selecting avg but running a query in my Zen page wouldn’t show me a time format

If you the field is defined as %Integer or %String, then this is incorrect. The field type must be %Time. In this case, a time will be stored in the database as number, and the query will display the time as a string.

Here is a small example:

select 166 seconds,%external(cast(+166.5 as TIME)) "avg"

Output:

seconds = 166
avg = 00:02:46
 
Simple sample
Vitaliy Serdtsev · Apr 12, 2022

By default, data in the global is stored as

glbD(ID)=$LB(%%CLASSNAME,prop1,prop2,..,propN)

The total size of the string cannot exceed 3641144. Therefore, if you have a field length >3.6E6, and there are several such fields, the limit is exceeded. To work around this, you need to change storage for your class.

For example so:

glbD(ID)=$LB(%%CLASSNAME)
glbD(ID,"prop1")=prop1
glbD(ID,"prop2")=prop2
...
glbD(ID,"propN")=propN
 
Simple example