go to post Vitaliy Serdtsev · Oct 24 There is a faster way without opening the object: Class dc.test Extends %Persistent { ClassMethod Test() { s classname="dc.test", storagename="Default" w $$$defMemberKeyGet(classname,$$$cCLASSstorage,storagename,$$$cSDEFdatalocation) } Storage Default { <Data name="testDefaultData"> <Value name="1"> <Value>%%CLASSNAME</Value> </Value> </Data> <DataLocation>^abc.1</DataLocation> <DefaultData>testDefaultData</DefaultData> <IdLocation>^dc.testD</IdLocation> <IndexLocation>^dc.testI</IndexLocation> <StreamLocation>^dc.testS</StreamLocation> <Type>%Library.CacheStorage</Type> } }Result: ^abc.1
go to post Vitaliy Serdtsev · Aug 19 See documentation for $SYSTEM.SQL.QueryToTable(): THIS METHOD IS DEPRECATED. Refer to $SYSTEM.SQL.Schema.QueryToTable instead.2021.1 Am I correct in saying that a create table as select is strictly as COPY operation of an existing table. Yes, see CREATE TABLE table AS SELECT query copies column definitions and column data from an existing table (or tables) into a new table based on the specified SELECT query.2022.1 To clarify, I used the $SYSTEM command to create table from query, because I kept getting errors with create table as select (didn't like my AS statement). What could have gone wrong there? Benjamin De Boe For that purpose, the CREATE TABLE .. AS SELECT .. syntax is offered by several other databases, and will appear in IRIS SQL soon. As a matter of fact, a developer is working on that command this very week :-) Because this feature has appeared since version IRIS 2022.1 (see link above).
go to post Vitaliy Serdtsev · Aug 4 .. s sql=0 s sql($i(sql))="select *" s sql($i(sql))=",%external(DOB)||' '||%external(DOBTime) DOBString" s sql($i(sql))=",TO_CHAR(DOB||','||DOBTime,'MM/DD/YYYY HH24:MI:SS') DOBString2" s sql($i(sql))="from dc.test" d st.%ExecDirect(.st,.sql).%Display() .. Result: USER>d ##class(dc.test).Test() ID(INTEGER) DOB(DATE) DOBTime(TIME) DOBString(VARCHAR) DOBString2(VARCHAR) 1 01.01.2020 23:59:59 01.01.2020 23:59:59 01/01/2020 23:59:59 2 31.12.2022 10:10:10 31.12.2022 10:10:10 12/31/2022 10:10:10
go to post Vitaliy Serdtsev · Aug 4 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 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 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 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 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 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.
