I am not a fan of this behavior but the LogicalToOdbc method does not convert the ValueList to DisplayList like the LogicalToDisplay method does.

You have 2 Options:

1) you can use %External(FieldName) in the  query, that will give you the DislayList value

2) you can write your own LogicalToOdbc method that does the same as the LogicalToDisplay does

The example I like for DependsOn involves Method Generators.  In the classes below I have 2 very basic Method Generators, one in each class.  When compiling Example.A the method generator must execute the MyMethod2 in Example.B as part of generating MyMethod1 so Example.B must be fully compiled before we can compile Example.A.  

Without the DependsOn when I import the 2 classes I get the following error:

Compiling class Example.A
ERROR #5002: Cache error: <CLASS DOES NOT EXIST>MyMethod2+1^Example.A.G1 *Example.B [MyMethod2+1^Example.A.G1:USER]
  > ERROR #5490: Error running generator for method 'MyMethod2:Example.A'
ERROR: Example.A.G1.int(MyMethod2+1) : <CLASS DOES NOT EXIST> : Do %code.WriteLine(" Write """ _ ##class(Example.B).MyMethod() _ """")
ERROR: Example.A.cls(MyMethod2) of generated code [MyMethod2+1^Example.A.G1:USER]
    > ERROR #5030: An error occurred while compiling class 'Example.A' [compile+43^%occClass:USER]
Compiling class Example.B
Compiling table Example.A
Compiling table Example.B
Compiling routine Example.A.1
Compiling routine Example.B.1
Detected 1 errors during compilation in 0.130s.


When I add DependsOn to Example.A the error goes away. When you look at the compile output you can see that the order has changed.  The compiler generates the class and the routine for Example.B, making it fully functional, before we do anything with Example.A.


Compilation started on 09/07/2016 12:47:58 with qualifiers 'fuckd'

Compiling 2 classes, using 2 worker jobs
Compiling class Example.B
Compiling routine Example.B.1
Compiling class Example.A
Compiling routine Example.A.1
Compilation finished successfully in 0.047s.



Here are my 2 classes:

Class Example.A Extends %Library.RegisteredObject [ DependsOn = Example.B ]

Method MyMethod1() [ CodeMode = objectgenerator ]
        Do %code.WriteLine(" Write """ _ ##class(Example.B).MyMethod2() _ """")
        Do %code.WriteLine(" Quit")
        Quit $$$OK



Class Example.B Extends %Library.RegisteredObject

ClassMethod MyMethod2() As %String [ CodeMode = objectgenerator ]
        Do %code.WriteLine(" set ReturnVal = ""This is a really bad example of a method generator """)
        Do %code.WriteLine(" Quit ReturnVal")
        Quit $$$OK


Man I need to learn how to read :(

So you are asking about COLLATE, not COLLATION.  Let me try again.

In Cache you can change the way data is sorted and other regional things (date and number formats for example) by using the National Language Support (NLS).  By selecting one of the available locales you can control the sort order of your data.

You can configure NLS setting via classes:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

or from the portal:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

There is also an article that give some examples of working with NLS:  https://community.intersystems.com/post/collations-cach%C3%A9

I hope this post is a little more in line with what you were looking for.

Can you give more details on what you are trying to do.?  Properties defined as %String have a default collation of SQLUPPER and any comparisons against those fields will be case insensitive.  

The only time this is not true if it the condition is against part of the IDKey.   The IDKey always uses EXACT collation.

You can force any collation you want by including it in the query like this:

SELECT Name FROM Sample.Person WHERE %UPPER(Name) LIKE 'A%'

You can use ZN to change namespace, do the INSERT and then ZN back.  The danger is trying to do stuff between the 2 namespaces with Objects.   If you open an object in one namespace, then ZN to a different namespace and try to reference the object it will get an error.

Cache and Ensemble 2015.2.2 Build 811 is now available on the distribution page of the WRC.


We are currently testing HealthShare and expect it to be available soon

Here is a little more detail on the scope of this alert.  This problem can happen when a non-cursor based query is nested in a second one and both  make use of a temp index (temp-file in Show Plan text).


Non-cursor based queries are:

SELECT - that return only 1 row.  These would only happen in Embedded SQL ie &SQL(SELECT….)

All INSERTs - can happen in Dynamic, xDBC or Embedded

All UPDATEs - can happen in Dynamic, xDBC or Embedded

All DELETEs   - can happen in Dynamic, xDBC or Embedded


Some examples of queries in the Samples Namespace that would generate problem code are:


&SQL(SELECT name, Age, $$Sample.Person_ UpdateProcTest FROM SQLUser.testidkey2 WHERE (:K1 is not null and Age = :K1) OR (:K1 IS NULL AND Age IS NULL))


INSERT INTO Sample.Person (Name, SSN) SELECT Name, SSN FROM Sample.Person WHERE (:K1 is not null and Age = :K1) OR (:K1 IS NULL AND Age IS NULL)


UPDATE Sample.Person SET Name = 'Bannon,Brendan' WHERE SSN = ?


DELETE FROM Sample.person WHERE name Like ? and Age > ?


If you look at Show Plans for these queries the thing that they all have in common and is the text that says the query can lead to the problem is building a temp-file.  This temp-file can have a couple of different formats so the text can vary some.  Two examples are:


 Add ID bit to bitmap temp-file A.




 Add a row to temp-file A, subscripted by a counter



For the problem to happen you would need to have 2 queries that both make use of a temp-file and one is nested inside the other.  Some examples of nesting would be:


  1. The SELECT above that has a $$ function call.  That Stored Procedure does an SQL UPDATE.
  2. A SELECT that has a Computed Field that uses a non-cursor base query.
  3. Issuing an INSERT UPDATE or DELETE that would invoke a trigger that contained a non-cursor based query.
  4. Doing an INSERT UPDATE or DELETE against a Parent class that uses relationships
  5. Doing an INSERT UPDATE or DELETE against a class that uses a Foreign Key with Cascade
  6. Doing a %Save() or a %Delete() against a class that uses cache SQL Storage and has triggers that contained a non-cursor based query.
  7. Doing a %Save() or a %Delete() against a Parent class that uses relationships
  8. Doing a %Save() or a %Delete() against a class that uses a Foreign Key with Cascade
  9. A Stored Procedure that contains a non-cursor based query and calls a second SP that also has a non-cursor based query
  10. ?????

March 3, 2016 – Alert: Incorrect SQL Results – Update

After further study of this problem, InterSystems has decided to replace all 2015.2.* full kits on the WRC distribution page. We have removed the current kits and will post new 2015.2.* kits as soon as they are available. We will also post an alert announcing that the new kits are available.

If you are using Cache 2015.2.*, Ensemble 2015.2.*, or a HealthShare kit based on 2015.2.*, InterSystems recommends that you get a full-kit Ad Hoc that contains the correction. Once you have upgraded to a new kit containing the correction, you must compile all the classes and routines in your application.

If you have any questions regarding this alert, please contact the Worldwide Response Center.

OK one more try.

When you use DISTINCT or GROUP BY we are going to build a temp global using the Field values as subscripts.  So say we have a query with GROUP BY Name, Title

the table has data that looks like this:

Scott     Customer

Brendan Support

Eduard Customer

Brendan Trouble Maker


To figure out the GROUP BY we will build a global that looks like this:


^||sql.temp(1,"Brendan","Trouble Maker")=""




With this data the longest global node is for that Trouble Maker, 39 total charaters.  This is well under 511 so no subscript error.  Now if I have a Title that is 500 characters long then the total length of the global would be over 511 and we would get a <SUBSCRIPT> error.

The more fields you put into a GROUP BY the easier it will be to get over the 511 charater limit as each field will be a different subscript in a global node.