go to post Vitaliy Serdtsev · Mar 19, 2020 Symbols Used in Caché SQL SELECT (CASE WHEN (order_description IS NULL OR (order_description='')) THEN '' ELSE (order_description) END) || (CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN '' ELSE (', ' || dosage || ' ' || dosage_unit || (CASE WHEN (dosage_form IS NULL OR (dosage_form='')) THEN '' ELSE (' ' || dosage_form) END) || '') END) || (CASE WHEN (administration_route IS NULL OR (administration_route='')) THEN '' ELSE (', ' || administration_route) END) || (CASE WHEN (frequency_description IS NULL OR (frequency_description='')) THEN '' ELSE (', ' || (CASE WHEN (quantity IS NULL OR quantity_unit IS NULL OR (quantity='') OR (quantity_unit='')) THEN '' ELSE (quantity || ' ' || quantity_unit || ' ') END) || frequency_description) END) || (CASE WHEN start_date IS NULL THEN '' ELSE (', ' || 'Start Date: ' || CONVERT(SQL_DATE,start_date,101)) END) As MedicationOrder FROM CWSOrderEntry.history_client_order WHERE PATID='100068' --AND FACILITY=?FACILITY AND order_status_code='A' AND order_type_category_code='P'Try changing the font, it's possible that you just have "||" displayed as "!!".
go to post Vitaliy Serdtsev · Mar 18, 2020 Using the JSON Adaptor. Simple example: Class dc.test Extends (%RegisteredObject, %JSON.Adaptor) { Property list As list Of %String; /// d ##class(dc.test).test() ClassMethod test() { s json={"list":["green","yellow,red","blue"]} s t=..%New() d t.%JSONImport(json) w t.list.Count(),!,t.list.GetAt(2) } } USER>d ##class(dc.test).test() 3 yellow,redAlso look at Using Document Database (DocDB). Another option without %ZEN.Auxiliary: Class dc.mylist Extends %ListOfDataTypes { Method SizeSet(newvalue As %Integer) As %Status { s i%Size=newvalue q $$$OK } } Class dc.test [ Abstract ] { /// d ##class(dc.test).test() ClassMethod test() { s json=["green","yellow,red","blue"] s t=##class(%Document.Object).CSON(json.%ToJSON()) s l=##class(dc.mylist).%New() m l.Data=t."_data" zk l.Data s l.Size=t.Count() w l.Count(),!,l.GetAt(2) } }
go to post Vitaliy Serdtsev · Mar 16, 2020 You can do this very simply ($get(array.Data("key"),"default")), for example: set array=##class(%ArrayOfDataTypes).%New() write $get(array.Data("oops"),"what?!"),! do array.SetAt("blabla","oops") write $get(array.Data("oops"),"what?!"),!
go to post Vitaliy Serdtsev · Mar 3, 2020 Defining and Using Stored Procedures Source code Class dc.test [ Abstract ] { Query daterange( d1 As %String, d2 As %String) As %Query(ROWSPEC = "dBegin:%String,dEnd:%String") [ SqlName = daterange, SqlProc ] { } ClassMethod daterangeExecute( ByRef qHandle As %Binary, d1 As %String, d2 As %String) As %Status { s qHandle("d1")=$system.SQL.TODATE(d1,"MM-DD-YYYY"), qHandle("d2")=$system.SQL.TODATE(d2,"MM-DD-YYYY") q $$$OK } ClassMethod daterangeFetch( ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = daterangeExecute ] { s d2=qHandle("d2"), dBegin=qHandle("d1"), dEnd=$system.SQL.LASTDAY(dBegin) s:dEnd>d2 dEnd=d2 i dBegin>d2 { s AtEnd=1 } else { s Row=$lb($system.SQL.TOCHAR(dBegin,"MM-DD-YYYY"),$system.SQL.TOCHAR(dEnd,"MM-DD-YYYY")), qHandle("d1")=dEnd+1 } q $$$OK } ClassMethod daterangeClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = daterangeExecute ] { q $$$OK } } Result: select * from dc.daterange('1-5-2019','5-25-2019') dBegin dEnd 01-05-2019 01-31-2019 02-01-2019 02-28-2019 03-01-2019 03-31-2019 04-01-2019 04-30-2019 05-01-2019 05-25-2019
go to post Vitaliy Serdtsev · Mar 3, 2020 Defining Stored Procedures Source code Class dc.test [ Abstract ] { Query numbers(count As %Integer = 4) As %Query(ROWSPEC = "n:%Integer") [ SqlName = numbers, SqlProc ] { } ClassMethod numbersExecute( ByRef qHandle As %Binary, count As %Integer = 4) As %Status { s qHandle=$lb(0,count) q $$$OK } ClassMethod numbersFetch( ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = numbersExecute ] { s n=$li(qHandle,1)+1 i n>$li(qHandle,2) { s AtEnd=1 } else { s Row=$lb(n) s $li(qHandle,1)=n } q $$$OK } ClassMethod numbersClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = numbersExecute ] { q $$$OK } } Result: select * from dc.numbers(107) n 1 2 ... 105 106 107
go to post Vitaliy Serdtsev · Feb 28, 2020 Forget. Now %STARTSWITH all other things being equal is slightly faster than LIKE. This point is deeply hidden in the documentation, and it seems that this applies only to FOR SOME %ELEMENT. If manage to speed up the special case for LIKE, then still need to correct/supplement the documentation.
go to post Vitaliy Serdtsev · Feb 27, 2020 Through %ZEN.proxyObject is unlikely to work, since the q parameter cannot be disabled in this case q - output numeric values unquoted even when they come from a non-numeric property Use your own class, for example: Class dc.test Extends %RegisteredObject { Property articlenumber As %String; }s object = ##class(dc.test).%New() s object.articlenumber = "15049950" s x = ##class(%ZEN.Auxiliary.jsonProvider).%WriteJSONStreamFromObject(.json,object,,,,"aelotw")Output: { "articlenumber":"15049950" }
go to post Vitaliy Serdtsev · Feb 27, 2020 Hi Evgeny! I can't check for technical reasons. Docker version does not suit me.
go to post Vitaliy Serdtsev · Feb 27, 2020 Conclusion: the quote will be removed from the documentation since it's no longer true. Then besides this, in the documentation for %STARTSWITH need to add the note DEPRECATED and the recommendation "use LIKE 'XXX%'" I also did an analysis for Caché 2018.1 Class del.t Extends %Persistent { Index ip On p; Property p As %VarString; /// d ##class(del.t).Fill() ClassMethod Fill(N = 1000000) { d DISABLE^%NOJRN k ^del.tD,^del.tI f i=1:1:N s ^del.tD(i)=$lb("","test"_i) s ^del.tD=N d ENABLE^%NOJRN d ..%BuildIndices(,,,$$$NO) d $system.SQL.TuneTable($classname(),$$$YES) d $system.OBJ.Compile($classname(),"cu-d") } }Although the plans are exactly the same in SMP the results of the metrics differ: select count(*) from del.t where p like 'test7%' Row count: 1 Performance: 0.291 seconds 333340 global references 2000537 lines executed select count(*) from del.t where p %startswith 'test7' Row count: 1 Performance: 0.215 seconds 333340 global references 1889349 lines executed For the next two queries, the INT code matches: &sql(select * from del.t where p like 'test7%') &sql(select * from del.t where p %startswith 'test7')But for these - is already different, so the metrics in SMP are different: &sql(select * from del.t where p like :a) &sql(select * from del.t where p %startswith :a)In IRIS 2020.1, the work with embedded queries was changed, but I can't check it.
go to post Vitaliy Serdtsev · Feb 26, 2020 Quote from the documentation: For performance reasons, the predicate %STARTSWITH 'abc' is preferable to the equivalent predicate LIKE 'abc%'. proof
go to post Vitaliy Serdtsev · Feb 26, 2020 I think that the author meant that the simplest queries of the form like 'text%' automatically worked as/converted to %startswith 'text'
go to post Vitaliy Serdtsev · Feb 25, 2020 I would look towards modern frameworks like React, Vue, etc., especially considering that CSP/ZEN/Mojo no longer develops.
go to post Vitaliy Serdtsev · Feb 25, 2020 The CSPBIND value is required at the compile-time to generate code on the client and server side. You try to change this value at runtime. See: CSP Page Lifecycle CSP Elements: Expressions
go to post Vitaliy Serdtsev · Feb 25, 2020 See: JSON Representation of a global structure Cogs Library Introducing InterSystems IRIS Document Database (DocDB)
go to post Vitaliy Serdtsev · Feb 24, 2020 If you have old version of Caché, you can use %ZEN.Auxiliary.jsonProvider or %ZEN.Auxiliary.altJSONProvider, which have a bunch of useful methods, for example: %ArrayToJSON %WriteJSONFromArray %WriteJSONStreamFromArray etc. Here are two small examples: set array=##class(%ListOfDataTypes).%New() for i=1:1:4000000 d array.Insert("item"_i) write "count = ",array.Count(),! do ##class(%ZEN.Auxiliary.altJSONProvider).%WriteJSONStreamFromObject(.stream,array,,,1,"aeloq") ; here you can save stream to a file or send it to the clientset meta=$lb("nameA","nameB","nameC") for i=1:1:4000000 set data(i)=$lb("itemA"_i,"itemB"_i,"itemC"_i) do ##class(%ZEN.Auxiliary.altJSONProvider).%ArrayToJSON(.meta,.data)
go to post Vitaliy Serdtsev · Feb 24, 2020 To Dmitry's words I will add a few links from the documentation: write [listname] (Caché List structured data displays in encoded form) List-Structure String Operations In your case, be the $LISTTOSTRING function is useful, for example: USER>set first=$lb("words","more","words") USER>write first words-morewords USER>write $listtostring(first) words,more,words USER>write $listtostring(first,"^") words^more^words USER>write $listtostring(first,"") wordsmorewordsI think that the result of "dogcatfish" in the book was due to copying/pasting, which caused the service characters to be lost. By the way, here on the forum it’s also not so easy to insert this gibberish ;)
