How do you count "Count"?
Why is "Northwest" 1 instead of 2 for 2021?

For now so:

select v.Branchnvl(sum(%FOREACH(v.Branch)),0) "Count" from 
  select 'Northwest' Branch,$listbuild('Northern','Western'Branches union
  select 'Oriental',$listbuild('Eastern'union
  select 'Southern',$listbuild('Southern')
  ) v
left join
  (select replace(%exact(Branch),' branch',''Branch,count(%FOREACH(Branch)) from yourtable where year("Date")=2021 group by Branchm
on m.Branch %inlist v.Branches
group by v.Branch

You are implicitly using %Library.SqlQuery:Func() method, in which, as @Robert Cemper rightly pointed out, $get() is used.

You can do it differently:

Query GetInfo(pObject AS Kurro.MyClassAs %SQLQuery(CONTAINID 1ROWSPEC "IdList:%String,IdProcess:%String,Duration:%String") [ SqlProc ]
    SELECT IdListIdProcessDuration
    FROM Kurro.MyClass
    WHERE KeyProcess :pObject.KeyProcess
    AND CodeSpecialist :pObject.CodeSpecialist
    AND CodeProvider :pObject.CodeProvider
    AND CodeCenter :pObject.CodeCenter
    AND "Date" :pObject.Date

set obj=##class(Kurro.MyClass).%New()
set obj.KeyProcess="1033004-1#"
set obj.CodeSpecialist "surgery"
set obj.CodeProvider "PR002"
set obj.CodeCenter "CENTER-01"
set obj.Date $ZDATETIME($ZDATETIMEH("2021-04-30 15:45:00",3,1),3,1)

set st=##class(%SQL.Statement).%New()
set sc=st.%PrepareClassQuery("Kurro.MyClass","GetInfo")
  if $$$ISERR(sc{write "%PrepareClassQuery failed:" do $System.Status.DisplayError(scquit}
set result=st.%Execute(obj)
do result.%Display()

Jobbed Process Permissions are Platform-dependent
Running Programs or System Commands with $ZF(-100)

So that we speak the same language, I made a simple example using Using the Work Queue Manager

Class dc.test Abstract ]

ClassMethod MyJob(SDIR As %String)



  X=$ZF(-1,$$$FormatText("DIR %1 >> %2",$$$quote(##class(%File).NormalizeDirectory(SDIR)),$$$quote(FILE)))

/// d ##class(dc.test).test()
ClassMethod test()
  i=1:1:queue.Queue("##class(dc.test).MyJob","C:\Temp\test "_i)


I copied different files to the following directories:

C:\Temp\test 1
C:\Temp\test 2
C:\Temp\test 3
C:\Temp\test 4

After calling ##class(dc.test).test() , everything worked out as expected: DIRLIST.TXT were created in the corresponding directories each with its own content.

See %Regex.Matcher


text "This is a sample blob of text",

w:matcher.Locate() "hit",!


while matcher.Locate() {write "Found ",matcher.Group," at position ",matcher.Start,!}

Found This at position 1
Found blob at position 18
Found text at position 26

Or see $locate: Using Regular Expressions in Caché


USER>w $locate(text,$tr(keywords,",","|"),1,e,x)

I keep my promise (yes, it wasn't an April Fool's joke ;)

  • The first way is associated with a dummy field for the sake of being able to override the final BuildValueArray method and avoid the following error
    ERROR #5272: Can't change final 'Method' : 'BuildValueArray')
    Class rcc.IC.ItemList Extends (%Persistent%Populate) [ Final ]
    Index xitmp On (ItemsP(KEYS), ItemsP(ELEMENTS));
    Property Company As %String Required ];
    Property Region As list Of %String(COLLATION "EXACT"POPSPEC ":4"VALUELIST ",US,CD,MX,EU,JP,AU,ZA") [ Required ];
    Property Items As list Of rcc.IC.serItem(POPSPEC ":4") [ Required ];
    Property ItemsP As %String(COLLATION "EXACT") [ CalculatedPrivateReadOnlyRequiredSqlComputeCode = {{*} {Items}}, SqlComputed ];
    ClassMethod ItemsPBuildValueArray(
      ByRef arrayAs %Status
      while $listnext(value,ptr,val){
      q $$$OK
    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow') and %value='Color')

    or if need to find values in any fields

    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

    For the sake of speed, you can store in the index not all the fields of the serial class, i.e.:

    Index xitmp On ItemsP(KEYS);
    ClassMethod ItemsPBuildValueArray(
      ByRef arrayAs %Status
      while $listnext(value,ptr,val){
          array($li(v,3))="" ; only Color
      q $$$OK
    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

    You can also add more dummy fields and accordingly indexes to cover all possible queries.

  • The second way involves changing the storage and creating a virtual table
    Class rcc.IC.ItemList Extends (%Persistent%Populate) [ Final ]
    Index xitm On Items(ELEMENTS).Color;
    Index xitm1 On (Items(ELEMENTS).Color, Items(KEYS));
    Property Company As %String Required ];
    Property Region As list Of %String(COLLATION "EXACT"POPSPEC ":4"VALUELIST ",US,CD,MX,EU,JP,AU,ZA") [ Required ];
    Property Items As list Of rcc.IC.serItem(POPSPEC ":4"STORAGEDEFAULT "array") [ Required ];
    select count(distinct IDfrom rcc_IC.ItemList where ItemList_Items->Items_Color in ('blue','yellow') -- index "xitm1" is used

    or even faster

    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(Items) (%Value in ('blue','yellow')) -- index "xitm" is used

    Of course, the data can be accessed from both tables, just do not forget about SetCollectionProjection GetCollectionProjection (for more information, see my article SQL index for array property elements)

Hi Robert.

I also got carried away with this question and found two more ways to use indexes for a list of serial objects, and you can explicitly specify specific fields in the query, rather than $list (%Value,3).

The speed may be not always the best, but I did the best I could. I tested on Caché (perhaps something has been improved in IRIS in this regard?)

If you're interested, I can share it.

Try the following:


This is not documented or supported. Use at your own risk.

But since you specified Caché 2018.1, which is no longer being developed so not may change in the future (not counting security patches).

    set t={"id":"1","Code"2}
    write t.%ToJSON(),!
    do t.%Set("Code","","null")
    write t.%ToJSON()