go to post Vitaliy Serdtsev · Aug 4, 2022 I like @Yaron Munz version better Class dc.test [ Abstract ] { ClassMethod Test() { s v=0, v($i(v))=$lb("2021-11-27","2022-08-04"), v($i(v))=$lb("2020-02-28","2023-02-27"), v($i(v))=$lb("2020-02-28","2023-03-01"), v($i(v))=$lb("2017-03-01","2020-02-28") f i=1:1:$o(v(""),-1) { s d1 = $li(v(i),1), d2 = $li(v(i),2), age1 = $e(d2,1,4)-$e(d1,1,4)-($e(d1,6,10)]$e(d2,6,10)), age2 = $number($zabs($zdh(d2,3)-$zdh(d1,3))/365.25,0), age3 = $zdh(d2,3)-$zdh(d1,3)\365.25 w d1," ",d2," (age1 = ",age1,", age2 = ",age2,", age3 = ",age3,")",! } } }Result: USER>d ##class(dc.test).Test() 2021-11-27 2022-08-04 (age1 = 0, age2 = 1, age3 = 0) 2020-02-28 2023-02-27 (age1 = 2, age2 = 3, age3 = 2) 2020-02-28 2023-03-01 (age1 = 3, age2 = 3, age3 = 3) 2017-03-01 2020-02-28 (age1 = 2, age2 = 3, age3 = 2)
go to post Vitaliy Serdtsev · Aug 4, 2022 Instead of two separate fields, it would be easier to use the %TimeStamp (or %PosixTime) type, where the date and time are paired at once. For this type of data, you can make your own indexes for different parts and/or combinations of them: a separate date, a separate time, a separate year, a separate year and month, etc. You can also use these separate parts in the query (in SELECT and WHERE). select datediff('yy',DOB,current_date) Age from yourtable
go to post Vitaliy Serdtsev · Jun 30, 2022 Class dc.test Extends (%RegisteredObject, %JSON.Adaptor) { Parameter %JSONENABLED = 1; Property AppointmentID As %String(%JSONFIELDNAME = "AppointmentID", %JSONINCLUDE = "inout"); Property AppointmentType As %String(%JSONINCLUDE = "inout"); Property AppointmentTypeID As %String(%JSONINCLUDE = "inout"); Property Date As %String(%JSONINCLUDE = "inout"); Property DepartmentID As %String(%JSONINCLUDE = "inout"); Property Duration As %Integer(%JSONINCLUDE = "inout"); Property PatientAppointmentTypeName As %String(%JSONINCLUDE = "inout"); Property LocalProviderID As %String(%JSONINCLUDE = "inout"); Property ProviderID As %String(%JSONINCLUDE = "inout"); Property StartTime As %String(%JSONINCLUDE = "inout"); Property Reason As %String(%JSONINCLUDE = "inout"); XData AthenaAppointment { <Mapping xmlns="http://www.intersystems.com/jsonmapping"> <Property Name="AppointmentID" FieldName="appointmentid" /> <Property Name="AppointmentType" FieldName="appointmenttype" /> <Property Name="AppointmentTypeID" FieldName="appointmenttypeid" /> <Property Name="Date" FieldName="date" /> <Property Name="DepartmentID" FieldName="departmentid" /> <Property Name="Duration" FieldName="duration" /> <Property Name="PatientAppointmentTypeName" FieldName="patientappointmenttypename" /> <Property Name="LocalProviderID" FieldName="localproviderid" /> <Property Name="ProviderID" FieldName="providerid" /> <Property Name="StartTime" FieldName="starttime" /> <Property Name="Reason" FieldName="reasonid" /> </Mapping> } /// d ##class(dc.test).Test() ClassMethod Test() { s json="{""date"":""06/27/2022"",""appointmentid"":""1214525"",""departmentid"":""195"",""localproviderid"":""187"",""appointmenttype"":""NEW PATIENT 45"",""providerid"":""187"",""starttime"":""14:00"",""duration"":45,""appointmenttypeid"":""1188"",""reasonid"":""-1"",""patientappointmenttypename"":""New Patient""}" try{ s tmp=..%New() $$$ThrowOnError(tmp.%JSONImport(json,"AthenaAppointment")) $$$ThrowOnError($system.OBJ.Dump(tmp)) }catch(ex){ #dim ex As %Exception.AbstractException w ex.DisplayString() } } }Output: USER>d ##class(dc.test).Test() +----------------- general information --------------- | oref value: 3 | class name: dc.test | reference count: 1 +----------------- attribute values ------------------ | AppointmentID = 1214525 | AppointmentType = "NEW PATIENT 45" | AppointmentTypeID = 1188 | Date = "06/27/2022" | DepartmentID = 195 | Duration = 45 | LocalProviderID = 187 |PatientAppointmentTypeName = "New Patient" | ProviderID = 187 | Reason = -1 | StartTime = "14:00" +-----------------------------------------------------PS: pay special attention to the reason field: is it a string or an array of strings?
go to post Vitaliy Serdtsev · Jun 30, 2022 The new version of IRIS 2022.2 has a new feature Columnar Storage, about which the documentation says the following: Choosing a storage layout is not an exact science. You might need to experiment with multiple layouts and run multiple query tests to find the optimal one. Therefore, you are unlikely to find an exact answer to your question. Usually, the more efficient the query is and there are "correct" indexes, the smaller the GREF and, accordingly, the shorter the execution time. But this is influenced by many factors, not just the above: see InterSystems SQL Optimization Guide
go to post Vitaliy Serdtsev · Jun 23, 2022 See $SYSTEM.SQL.SetServerInitCode() (there are differences for IRIS) Simple example: Class dc.test { ClassMethod Test() { s programname=$zcvt(##class(%SYS.ProcessQuery).%OpenId($j).ClientExecutableName,"L") i programname="blablabla.exe" { ;useful work s $EC="ERROR" ;or s $ROLES="r1" } } } USER>d $SYSTEM.SQL.SetServerInitCode("d ##class(dc.test).Test()")Now, when connecting from a specific program via ODBC/JDBC to namespace "USER", an error will occur. You can configure something another.
go to post Vitaliy Serdtsev · Jun 23, 2022 See Parent-Child Relationships and Storage Demonstration: Class dc.child Extends %Persistent { Property name; Property parent As dc.parent; // Relationship parent As dc.parent [ Cardinality = parent, Inverse = child ]; } Class dc.parent Extends %Persistent { Property name; // Relationship child As dc.child [ Cardinality = children, Inverse = parent ]; ClassMethod Test() { d ..%KillExtent() d ##class(child).%KillExtent() &sql(insert into dc.parent(name) values('parent1')) &sql(insert into dc.parent(name) values('parent2')) &sql(insert into dc.child(name,parent) values('child11',1)) &sql(insert into dc.child(name,parent) values('child12',1)) &sql(insert into dc.child(name,parent) values('child21',2)) &sql(insert into dc.child(name,parent) values('child22',2)) zw ^dc.parentD,^dc.childD } }USER>d ##class(dc.parent).Test() ^dc.parentD=2 ^dc.parentD(1)=$lb("","parent1") ^dc.parentD(2)=$lb("","parent2") ^dc.childD=4 ^dc.childD(1)=$lb("","child11",1) ^dc.childD(2)=$lb("","child12",1) ^dc.childD(3)=$lb("","child21",2) ^dc.childD(4)=$lb("","child22",2) Important: do not touch the existing Storages in both classes!!! Class dc.child Extends %Persistent { Property name; //Property parent As dc.parent; Relationship parent As dc.parent [ Cardinality = parent, Inverse = child ]; Storage Default { ... } } Class dc.parent Extends %Persistent { Property name; Relationship child As dc.child [ Cardinality = children, Inverse = parent ]; ClassMethod Test() { ... } Storage Default { ... } }USER>d ##class(dc.parent).Test() ^dc.parentD=2 ^dc.parentD(1)=$lb("","parent1") ^dc.parentD(2)=$lb("","parent2") ^dc.childD=4 ^dc.childD(1,1)=$lb("","child11",1) ^dc.childD(1,2)=$lb("","child12",1) ^dc.childD(2,3)=$lb("","child21",2) ^dc.childD(2,4)=$lb("","child22",2) Important: now remove Storage from dc.child class and recompile both classes. Note that now the Storage of the dc.child class has changed. USER>d ##class(dc.parent).Test() ^dc.parentD=2 ^dc.parentD(1)=$lb("","parent1") ^dc.parentD(1,"child",1)=$lb("","child11") ^dc.parentD(1,"child",2)=$lb("","child12") ^dc.parentD(2)=$lb("","parent2") ^dc.parentD(2,"child",3)=$lb("","child21") ^dc.parentD(2,"child",4)=$lb("","child22") ^dc.childD=4 ^dc.childD(1,1)=$lb("","child11",1) ^dc.childD(1,2)=$lb("","child12",1) ^dc.childD(2,3)=$lb("","child21",2) ^dc.childD(2,4)=$lb("","child22",2)Now the data in ^dc.childD from the previous test/step is hanging in the air and cannot be accessed via SQL Based on the above, the answer to your question will depend on what and how exactly you changed in your classes. PS: for simplicity, I would advise you to create a clone of your dc.child class (without Relationship) and already take the "disappeared" data from it. After linking the tables (possibly with subsequent copying of data from the old Storage to the new one), the clone with the data can be deleted.
go to post Vitaliy Serdtsev · Jun 23, 2022 See MultiValue Basic | Caché Alternative Exists for SOUNDEX() Workaround: Class dc.test [ Abstract ] { ClassMethod Test() { w ..SOUNDEX("M"),! ;or &sql(select SOUNDEX('McD') into :r) w r,! } ClassMethod SOUNDEX(s) As %String [ Language = mvbasic, SqlName = SOUNDEX, SqlProc ] { RETURN SOUNDEX(s) } } Result: USER>d ##class(dc.test).Test() M000 M230
go to post Vitaliy Serdtsev · Apr 27, 2022 I already tried option 1 with Timeformat=1 for the avg and didn't work. Have you changed the TimeFormat at the process or system level? If at the process level, then most likely you have changed this value in one process, and are executing the query in another. Therefore, there is no effect. If at the system level, then the query should work. To avoid uncertainty, change the definition of the field Property PackingTimeSpent As %Time; and execute in SMP for Display Mode select PackingTimeSpent from MSDS_Serenity.KitlabelAssoc The hh:mm:ss format string should be displayed. so eg. select %external(CAST(+avg(166.38) as TIME)) didn't work for me Two remarks: this will work as intended only if you have changed the TimeFormat=1 at the system level avg(166.38) - it's pointless to write like that. you mentioned the example above and I'm not sure to which one are youi refering to with so many replies, so can you point me to which one exactly See Time-to-String Conversionlink Try this (does not depend on the TimeFormat value of the current locale): select TO_CHAR(avg(PackingTimeSpent),'HH24:MI:SS') average from MSDS_Serenity.KitlabelAssoc where label='00007IT4'PS: By the way, I noticed in your screenshots the differences in queries: somewhere you write where label='00007IT4'somewhere you write where ID='00007IT4' Is this how it should be? To avoid unnecessary questions, please do not change everytime the names of the fields in the queries. This is misleading.
go to post 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: create a new class with the same structure, but with a new primary key; move data from the old class into it using SQL (not the merge command); delete data/indexes in the old class, then change the primary key in it; move data from the new class to the old class, using the merge command; delete the new class with the data; 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.
go to post 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: if in the current locale TimeFormat = 1 select %external(CAST(+166.38 as TIME)) Important: it is necessary to convert a float number to an integer type, otherwise you will get zero. if in the current locale TimeFormat <> 1 select TO_CHAR(166.38,'HH24:MI:SS')
go to post Vitaliy Serdtsev · Apr 26, 2022 Try other methods/queries of this class, such as Dump*, ProcessList, ConnectionList, etc., which return the number of connections.
go to post 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?
go to post Vitaliy Serdtsev · Apr 18, 2022 This may be useful to you: LogRollback Transactions and Savepoints Rolling Back Incomplete Transactions
go to post Vitaliy Serdtsev · Apr 13, 2022 As I wrote above, there are two ways to solve: change TimeFormat in the locale. Query in this case will be simple. See ##class(%SYS.NLS.Format).SetFormatItem, Configuring National Language Support (NLS). complicate query. See Time-to-String Conversion Which option do you choose?
go to post Vitaliy Serdtsev · Apr 13, 2022 Most likely, your locale uses TimeFormat = 2 (see tformat) You can change your locale or explicitly specify the format of the field, for example like this: Property PackingTimeSpent As %Time(FORMAT = 1);
go to post 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 Class dc.test Extends %Persistent { Index mySimilarityIndex On SettingsJSON(KEYS) [ Data = SettingsJSON(ELEMENTS) ]; Index mySimilaritybackupIndex On SettingsJSONbackup(KEYS) [ Data = SettingsJSONbackup(ELEMENTS) ]; Property SettingsJSON As %Text(LANGUAGECLASS = "%Text.English", MAXLEN = 3600000, SIMILARITYINDEX = "mySimilarityIndex"); Property SettingsJSONbackup As %Text(LANGUAGECLASS = "%Text.English", MAXLEN = 3600000, SIMILARITYINDEX = "mySimilaritybackupIndex"); ClassMethod Test() { d ..%KillExtent() s json=$tr($j("",3600000)," ","0") &sql(insert into dc.test(SettingsJSON,SettingsJSONbackup) values(:json,:json)) w $l(json),":",SQLCODE } Storage Default { <Data name="backup"> <Attribute>SettingsJSONbackup</Attribute> <Structure>node</Structure> <Subscript>"SettingsJSONbackup"</Subscript> </Data> <Data name="json"> <Attribute>SettingsJSON</Attribute> <Structure>node</Structure> <Subscript>"SettingsJSON"</Subscript> </Data> <Data name="testDefaultData"> <Value name="1"> <Value>%%CLASSNAME</Value> </Value> </Data> <DataLocation>^dc.testD</DataLocation> <DefaultData>testDefaultData</DefaultData> <IdLocation>^dc.testD</IdLocation> <IndexLocation>^dc.testI</IndexLocation> <StreamLocation>^dc.testS</StreamLocation> <Type>%Library.CacheStorage</Type> } }
go to post Vitaliy Serdtsev · Apr 12, 2022 Choose according to your taste: #include %systemInclude s s=$c(34,34)_"te""""st"_$c(34,34) w s,!, ##class(%Global).UndoubleInnerQuotes(s),!, $$$StripQuotes(s),!, $tr(s,$c(34),"")Output: USER>d ^test ""te""st"" "te"st" "te"st" test
go to post Vitaliy Serdtsev · Apr 11, 2022 Here's what I found: IndexClass (%Dictionary.ClassDefinition) IndexClass (%Dictionary.CompiledClass) That is, you can write something like this: Class dc.test Extends %Persistent [ IndexClass = dc.anothertest ] { }The same applies to MemberSuper. Unfortunately, I haven't found any examples of how to use it or if it works at all.
go to post Vitaliy Serdtsev · Apr 11, 2022 Class dc.test Extends %Persistent { Property F As %Integer; ClassMethod Test() { d ..%KillExtent() &sql(insert into dc.test(F) values(22)) &sql(insert into dc.test(F) values(11)) s label = "00007I0Q" &sql(select ID into :cnt from dc.test where ID = :label) w SQLCODE,":",cnt } }Output for Caché 2018.1: 100:00007I0Q Output for IRIS 2021.2: 100: PS: it is a pity that my answer was ignored.