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).
..
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()
..
Class dc.test Extends %Persistent
{
Property DOB As %Date;
Property DOBTime As %Time;
ClassMethod Test()
{
d ..%KillExtent()
&sql(insert into dc.test(DOB,DOBTime)
select {d '2020-01-01'},{t '23:59:59'}
union all
select {d '2022-12-31'},{t '10:10:10'})
s st=##class(%SQL.Statement).%New()
s st.%SelectMode=2
s sql=5
s sql(1)="select *"
s sql(2)=",%odbcout(DOB)||' '||%odbcout(DOBTime) DOBString"
s sql(3)=",CAST(%odbcout(DOB)||' '||%odbcout(DOBTime) as DATETIME) DOBDateTime"
s sql(4)=",UNIX_TIMESTAMP(DOB||','||DOBTime) DOBUnixTimeStamp"
s sql(5)="from dc.test"
d st.%ExecDirect(.st,.sql).%Display()
}
}
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
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
Because this feature has appeared since version IRIS 2022.1 (see link above).
go to post
USER>d $system.License.ConnectionListFunc().%Display()
or
d $system.License.ProcessListFunc().%Display()
go to post
Try to play around with the SetConnectionLimit() & SetUserLimit() methods.
PS: I do not know what about DBeaver, but DbVisualizer has the ability to configure one physical connection.
go to post
I think it is necessary to focus on the $System.License.InstanceConnections()
PS: and is better in my opinion to use $SYSTEM.License.KeyPlatform(), not $SYSTEM.License.KeyCustomerName()
go to post
Result:
go to post
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:
go to post
I think this is where the confusion lies, because the documentation says about 5 connections, not license units
go to post
See my old article Indexing of non-atomic attributes, chapter "Date (time, etc.)"
go to post
A simple example of combining:
Class dc.test Extends %Persistent { Property DOB As %Date; Property DOBTime As %Time; ClassMethod Test() { d ..%KillExtent() &sql(insert into dc.test(DOB,DOBTime) select {d '2020-01-01'},{t '23:59:59'} union all select {d '2022-12-31'},{t '10:10:10'}) s st=##class(%SQL.Statement).%New() s st.%SelectMode=2 s sql=5 s sql(1)="select *" s sql(2)=",%odbcout(DOB)||' '||%odbcout(DOBTime) DOBString" s sql(3)=",CAST(%odbcout(DOB)||' '||%odbcout(DOBTime) as DATETIME) DOBDateTime" s sql(4)=",UNIX_TIMESTAMP(DOB||','||DOBTime) DOBUnixTimeStamp" s sql(5)="from dc.test" d st.%ExecDirect(.st,.sql).%Display() } }Result:
go to post
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