Multi Language Sort
I'm facing a specific sort problem.
There are several thousands of articles sold all over.
Users expect to get a description in local language sorted by their specific collation.
I have simplified the original class to the core fields for this case.
{
Property ArtNr As %Integer(MINVAL = 0) [ Required ];
/// default Description
Property Description As %String [ Required ];
/// German
Property DescDE As %String;
/// Italian
Property DescIT As %String;
/// English
Property DescEN As %String;
/// French
Property DescFR As %String;
/// Czech
Property DescCZ As %String;
/// Slovakian
Property DescSL As %String;
/// Hungarian
Property DescHU As %String;
/// Bulgarian
Property DescBG As %String;
/// Romanian
Property DescRO As %String;
/// Slovenian
Property DescSI As %String;
Index Idx On ArtNr [ IdKey ];
Index IdxDE On DescDE;
Index IdxHU On DescHU;
Index IdxDE On DescEN;
. . .
}
The whole construct serves as multi language lookup table.
It works based on the defined Indices [here only 3 for this example]
The problem to be solved is to have the correct language-specific collation
Some languages overlap nicely (eg. EN + DE) but others (esp. HU) don't match
or are totally separated (e.g. BG in Cyrillic notation)
just see example: https://en.wikipedia.org/wiki/Hungarian_alphabet
There is actually some hand-coded workaround in place to enforce collation but performance is not thrilling.
I hope you have some brilliant suggestions to get better solutions.
Comments
I will publish the actual workaround here later when there are other solutions or proposals
in order not to influence your creativity.
########################################################################
OK, There wasn't much echo so far.
The workaround was to encode the Description (and limit it for indexing) according to the language used.
The encoding is basically the position in the list of allowed/used characters or character groups.
If we use also numbers and interpunctuation it has to go into our list.
Instead of %String the descriptions are stored in a serial class:
{
Property Language As %String [ InitialExpression = 0 ];
Property Description As %String(MAXLEN = "");
Property Sort As %String(COLLATION = "EXACT", MAXLEN = 300) [ SqlComputed,
,SqlComputeCode = { Set {*} = ##class(DC.ArticleSort).Encode({Language},{Description}) }
,SqlComputeOnChange = (Description, Language) ];
ClassMethod Encode( Lang As %String, Desc As %String(MAXLEN="")) As %String(MAXLEN=300)
{ ...replace valid characters by binary postion value .. }
}
It's obvious that for English and your installed NLS no encoding is required.
For triplets or doublets like in Hungarian, you need several runs to encode
The Query itself has a minor change
WHERE DescHU_Description %STARTSWITH 'BE'
ORDER BY DescHU_Sort
The encoding is definitely not fast but change frequency is not an issue here and change happens
by typing in descriptions on the keyboard.
Query performance is supported by this index construct:
Index IdxHU On DescHU.Sort [ Data = (ArtNr, DescHU.Description) ];
So the query plan just iterates over this index.
#1) thanks for the Reminder on IRIS
#1) + #2) concentrate on finding. but that's not the key issues.
As an example: The problem becomes visible when you run
The problem is to get control of ORDER which depends on global collation.
Example for German:
- With Standard collation, you sort A,B,C..,O,...,U,...Z,Ä,Ö,Ü... (classic ANSI sort)
- With collation German3, you sort A,Ä,B,C,....O,Ö,....U,Ü,....Z
It is mostly the handling of characters with diacritical signs.
Hungarian is my worst case with much more diacriticals AND groups of characters in total 44 "character" tokens
A Á B C Cs D Dz Dzs E É F G Gy H I Í J K L Ly M N Ny O Ó Ö Ő P Q R S Sz T Ty U Ú Ü Ű V W X Y Z Zs
If you do it correctly then Cx sorts before Cs and Gz should sort before Gy and so on.
Now for ORDER BY you typically get your default collation derived from NLS settings.
#3) custom index seems to offer the most promising features.
Vitaliy,
That's excellent. I had the vague feeling that it should be somewhere, somehow, ... ass these are all supported collations.
Now I have the correct solution!
Many thanks !
What's the purpose of a normal index over a text field? I assume user searches by a substring. There are two and a half ways to perform a fulltext search:
- Define a property of %Text type and use language class. New language classes an be added fairly easily. Documentation. Removed in InterSystems IRIS and therefore not recommended.
- Use iFind indices. They are highly customisable.
- Use iFind indices creatively. You can build your own indices based on iFind indices and you can write your own tokenizers, etc. Also it's possible to define your own custom index. Community article on that.
Useful links:
This issue dares relatively simply:- Create the required collation tables. Fortunately, for some languages, they are already included and can be taken as a basis. In complex cases, you can contact to WRC.
Check their list and status:
USER>zn "%SYS"
%SYS><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">^COLLATE</FONT>
Status Number Abbrev Name
Built-in 0 OANS ISM Pre-6.2 Built-in 1 ANSI ISM 6.2->6.4 Built-in 2 COBR Ipsum/Cobra Built-in 3 DTMC DTM-compatible Built-in 4 CBR2 Ipsum/Cobra-2 Built-in 5 UNIC Cache standard Not loaded 10 GER1 German1 Not loaded 11 POR1 Portuguese1 Not loaded 12 POL1 Polish1 Not loaded 13 GER2 German2 Not loaded 14 SPA1 Spanish1 Not loaded 15 DAN1 Danish1 Available 16 CYR1 Cyrillic1 Not loaded 17 GRE1 Greek1 Not loaded 18 CZE1 Czech1 Not loaded 19 CZE2 Czech2 Not loaded 20 POR2 Portuguese2 Not loaded 21 FIN1 Finnish1 Not loaded 22 JAP1 Japanese1 Not loaded 24 POL2 Polish2 Not loaded 27 FRE1 French1 Not loaded 28 FIN2 Finnish2 Available 29 HUN1 Hungarian1Available 30 GER3 German3 Not loaded 31 POL3 Polish3 Not loaded 32 SPA2 Spanish2 Not loaded 33 DAN2 Danish2 Not loaded 34 GRE2 Greek2 Not loaded 35 FIN3 Finnish3 Not loaded 36 LIT1 Lithuanian1 Available 37 CYR3 Cyrillic3 Not loaded 38 SLO1 Slovenian1 Not loaded 39 SLO2 Slovenian2 Not loaded 40 TUR1 Turkish1 Not loaded 41 DAN3 Danish3 Available 42 UKR1 Ukrainian1 Available 43 CYR4 Cyrillic4 Not loaded 44 CZE3 Czech3 Not loaded 46 MAL1 Maltese1 Not loaded 48 MAL2 Maltese2 Not loaded 49 SPA4 Spanish4 Not loaded 50 SLO1 Slovak1 Not loaded 51 SPA5 Spanish5 Not loaded 52 FIN4 Finnish4 Not loaded 53 CZE4 Czech4 Built-in 128 OSTR ISM Pre-6.2 string Built-in 129 NSTR ISM 6.2->6.4 string Built-in 133 USTR Cache standard string Available 144 CYR1S Cyrillic1 string Available 157 HUN1S Hungarian1 string Available 158 GER3S German3 string Available 165 CYR3S Cyrillic3 string Available 170 UKR1S Ukrainian1 string Available 171 CYR4S Cyrillic4 string
- load the new collation tables to your current locale
- for Storage make mapping your indexes in separate globals with own collation
- Profit!
Class dc.test1 Extends %Persistent {Result:</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iDE On DescDE </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">EXACT;
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iHU On DescHU </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">EXACT;
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iRU On DescRU </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">EXACT;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">DescDE </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">DescHU </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">DescRU </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">LoadColTables() [ </FONT><FONT COLOR="#000080">Private </FONT><FONT COLOR="#000000">] { </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">r</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$$$NO try </FONT><FONT COLOR="#800080">{ </FONT><FONT COLOR="#0000ff">$$$AddAllRoleTemporaryInTry n $namespace s $namespace</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"%SYS" </FONT><FONT COLOR="#0000ff">$$$ThrowOnError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">Config.NLS.Tables</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Load</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"deuw"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"COL"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"German3"</FONT><FONT COLOR="#000000">)) </FONT><FONT COLOR="#0000ff">$$$ThrowOnError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">Config.NLS.Tables</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Load</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"hunw"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"COL"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Hungarian1"</FONT><FONT COLOR="#000000">)) </FONT><FONT COLOR="#0000ff">$$$ThrowOnError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">Config.NLS.Tables</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Load</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"rusw"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"COL"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Cyrillic4"</FONT><FONT COLOR="#000000">)) </FONT><FONT COLOR="#008000">;d ^COLLATE ; for check only
</FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">r</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$$$YES </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">catch</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">ex</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#800080">{ </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#800000">ex</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayString</FONT><FONT COLOR="#000000">(),! </FONT><FONT COLOR="#800080">}
</FONT><FONT COLOR="#0000ff">q </FONT><FONT COLOR="#800000">r </FONT><FONT COLOR="#000000">}
</FONT><FONT COLOR="#000080">/// d ##class(dc.test1).Test() ClassMethod </FONT><FONT COLOR="#000000">Test() { </FONT><FONT COLOR="#0000ff">q</FONT><FONT COLOR="#000000">:'..</FONT><FONT COLOR="#0000ff">LoadColTables</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test1</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">DescDE</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">DescHU</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">DescRU</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Straßer'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'csak'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'Прохор' </FONT><FONT COLOR="#000080">union all </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Straster'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'comb'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'Павел' </FONT><FONT COLOR="#000080">union all </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Strasser'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'cukor'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'Пётр'</FONT><FONT COLOR="#800080">) </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^dc.test1D </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!,</FONT><FONT COLOR="#008000">"---- Before (for all indexes by default = 'Cache standard') --------"</FONT><FONT COLOR="#000000">,!! </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#ff0000">print </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!,</FONT><FONT COLOR="#008000">"---- After (each index has its own collation) --------"</FONT><FONT COLOR="#000000">,!! </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%PurgeIndices</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%GlobalEdit</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Create</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"dc.test1.iDE"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Collate</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">DisplayToLogical</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"German3"</FONT><FONT COLOR="#000000">))) ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%GlobalEdit</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Create</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"dc.test1.iHU"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Collate</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">DisplayToLogical</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Hungarian1"</FONT><FONT COLOR="#000000">))) ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%GlobalEdit</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Create</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"dc.test1.iRU"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Collate</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">DisplayToLogical</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Cyrillic4"</FONT><FONT COLOR="#000000">))) ,..</FONT><FONT COLOR="#0000ff">%BuildIndices</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#0000ff">$$$NO</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$$$NO</FONT><FONT COLOR="#000000">) ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">) ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Compile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#008000">"cu-d"</FONT><FONT COLOR="#000000">) ,</FONT><FONT COLOR="#ff0000">print
</FONT><FONT COLOR="#0000ff">q
</FONT><FONT COLOR="#ff0000">print </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^dc.test1.iDE,^dc.test1.iHU,^dc.test1.iRU </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">! </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">lng</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"DE"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"HU"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"RU" </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#0000ff">$$$FormatText</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"select %ID,Desc%1 from dc.test1 order by %exact(Desc%1)"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">lng</FONT><FONT COLOR="#000000">)).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!! </FONT><FONT COLOR="#0000ff">q </FONT><FONT COLOR="#000000">}
</FONT><FONT COLOR="#000080">Storage </FONT><FONT COLOR="#000000">Default { <Data name="</FONT><FONT COLOR="#000080">test1DefaultData</FONT><FONT COLOR="#000000">"> <Value name="</FONT><FONT COLOR="#000080">1</FONT><FONT COLOR="#000000">"> <Value>%%CLASSNAME</Value> </Value> <Value name="</FONT><FONT COLOR="#000080">2</FONT><FONT COLOR="#000000">"> <Value>DescDE</Value> </Value> <Value name="</FONT><FONT COLOR="#000080">3</FONT><FONT COLOR="#000000">"> <Value>DescHU</Value> </Value> <Value name="</FONT><FONT COLOR="#000080">4</FONT><FONT COLOR="#000000">"> <Value>DescRU</Value> </Value> </Data> <DataLocation>^dc.test1D</DataLocation> <DefaultData>test1DefaultData</DefaultData> <IdLocation>^dc.test1D</IdLocation> <Index name="</FONT><FONT COLOR="#000080">iDE</FONT><FONT COLOR="#000000">"> <Location>^dc.test1.iDE</Location> </Index> <Index name="</FONT><FONT COLOR="#000080">iHU</FONT><FONT COLOR="#000000">"> <Location>^dc.test1.iHU</Location> </Index> <Index name="</FONT><FONT COLOR="#000080">iRU</FONT><FONT COLOR="#000000">"> <Location>^dc.test1.iRU</Location> </Index> <IndexLocation>^dc.test1I</IndexLocation> <StreamLocation>^dc.test1S</StreamLocation> <Type>%Library.CacheStorage</Type> }
}</FONT>
USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">dc.test1</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT> ^dc.test1D=3 ^dc.test1D(1)=$lb("","Straßer","csak","Прохор") ^dc.test1D(2)=$lb("","Straster","comb","Павел") ^dc.test1D(3)=$lb("","Strasser","cukor","Пётр")<FONT style="Background-COLOR:red;color:yellow">---- Before (for all indexes by default = 'Cache standard') --------</FONT>
^dc.test1.iDE("Strasser",3)="" ^dc.test1.iDE("Straster",2)="" ^dc.test1.iDE("Straßer",1)="" ^dc.test1.iHU("comb",2)="" ^dc.test1.iHU("csak",1)="" ^dc.test1.iHU("cukor",3)="" ^dc.test1.iRU("Павел",2)="" ^dc.test1.iRU("Прохор",1)="" ^dc.test1.iRU("Пётр",3)=""
ID DescDE 3 Strasser 2 Straster 1 Straßer
3 Rows(s) Affected
ID DescHU 2 comb 1 csak 3 cukor
3 Rows(s) Affected
ID DescRU 2 Павел 1 Прохор 3 Пётр
3 Rows(s) Affected
<FONT style="background-color:green;color:yellow">---- After (each index has its own collation) --------</FONT>
^dc.test1.iDE("Strasser",3)="" ^dc.test1.iDE("Straßer",1)="" ^dc.test1.iDE("Straster",2)="" ^dc.test1.iHU("comb",2)="" ^dc.test1.iHU("cukor",3)="" ^dc.test1.iHU("csak",1)="" ^dc.test1.iRU("Павел",2)="" ^dc.test1.iRU("Пётр",3)="" ^dc.test1.iRU("Прохор",1)=""
ID DescDE 3 Strasser 1 Straßer 2 Straster
3 Rows(s) Affected
ID DescHU 2 comb 3 cukor 1 csak
3 Rows(s) Affected
ID DescRU 2 Павел 3 Пётр 1 Прохор
3 Rows(s) Affected
Class dc.test2 Extends %Persistent {Result:</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iDE On (Lng, Descr </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">EXACT);
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iHU On (Lng, Descr </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">EXACT);
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iRU On (Lng, Descr </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">EXACT);
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">Lng </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">Descr </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">/// d ##class(dc.test2).Test() ClassMethod </FONT><FONT COLOR="#000000">Test() { </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">() ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%GlobalEdit</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Create</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"dc.test2.iDE"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Collate</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">DisplayToLogical</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"German3"</FONT><FONT COLOR="#000000">))) ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%GlobalEdit</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Create</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"dc.test2.iHU"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Collate</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">DisplayToLogical</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Hungarian1"</FONT><FONT COLOR="#000000">))) ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%GlobalEdit</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Create</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"dc.test2.iRU"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Collate</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">DisplayToLogical</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Cyrillic4"</FONT><FONT COLOR="#000000">)))
</FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test2</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">Lng</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">Descr</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'DE'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'Straßer' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'DE'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'Straster' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'DE'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'Strasser' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'HU'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'csak' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'HU'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'comb' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'HU'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'cukor' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'RU'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'Прохор' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'RU'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'Павел' </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'RU'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008080">'Пётр'</FONT><FONT COLOR="#800080">) </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^dc.test2D,^dc.test2.iDE,^dc.test2.iHU,^dc.test2.iRU </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">) ,</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Compile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#008000">"cu-d"</FONT><FONT COLOR="#000000">) ,</FONT><FONT COLOR="#ff0000">print
</FONT><FONT COLOR="#0000ff">q
</FONT><FONT COLOR="#ff0000">print </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">! </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">lng</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"DE"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"HU"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"RU" </FONT><FONT COLOR="#800080">{ </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">idx</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$replace</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"iDE,iHU,iRU"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"i"</FONT><FONT COLOR="#000000">_</FONT><FONT COLOR="#800000">lng</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"i"</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#800000">lng</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#0000ff">$$$FormatText</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"select * from %IGNOREINDEX %1 dc.test2 where Lng=? order by %exact(Descr)"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">idx</FONT><FONT COLOR="#000000">),</FONT><FONT COLOR="#800000">lng</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!! </FONT><FONT COLOR="#800080">} </FONT><FONT COLOR="#0000ff">q </FONT><FONT COLOR="#000000">}
</FONT><FONT COLOR="#000080">Storage </FONT><FONT COLOR="#000000">Default { <Data name="</FONT><FONT COLOR="#000080">test2DefaultData</FONT><FONT COLOR="#000000">"> <Value name="</FONT><FONT COLOR="#000080">1</FONT><FONT COLOR="#000000">"> <Value>%%CLASSNAME</Value> </Value> <Value name="</FONT><FONT COLOR="#000080">2</FONT><FONT COLOR="#000000">"> <Value>Lng</Value> </Value> <Value name="</FONT><FONT COLOR="#000080">3</FONT><FONT COLOR="#000000">"> <Value>Descr</Value> </Value> </Data> <DataLocation>^dc.test2D</DataLocation> <DefaultData>test2DefaultData</DefaultData> <IdLocation>^dc.test2D</IdLocation> <Index name="</FONT><FONT COLOR="#000080">iDE</FONT><FONT COLOR="#000000">"> <Location>^dc.test2.iDE</Location> </Index> <Index name="</FONT><FONT COLOR="#000080">iHU</FONT><FONT COLOR="#000000">"> <Location>^dc.test2.iHU</Location> </Index> <Index name="</FONT><FONT COLOR="#000080">iRU</FONT><FONT COLOR="#000000">"> <Location>^dc.test2.iRU</Location> </Index> <IndexLocation>^dc.test2I</IndexLocation> <StreamLocation>^dc.test2S</StreamLocation> <Type>%Library.CacheStorage</Type> }
}</FONT>
USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">dc.test2</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT> ^dc.test2D=9 ^dc.test2D(1)=$lb("","DE","Straßer") ^dc.test2D(2)=$lb("","DE","Straster") ^dc.test2D(3)=$lb("","DE","Strasser") ^dc.test2D(4)=$lb("","HU","csak") ^dc.test2D(5)=$lb("","HU","comb") ^dc.test2D(6)=$lb("","HU","cukor") ^dc.test2D(7)=$lb("","RU","Прохор") ^dc.test2D(8)=$lb("","RU","Павел") ^dc.test2D(9)=$lb("","RU","Пётр") ^dc.test2.iDE(" DE","Strasser",3)="" ^dc.test2.iDE(" DE","Straßer",1)="" ^dc.test2.iDE(" DE","Straster",2)="" ^dc.test2.iDE(" HU","comb",5)="" ^dc.test2.iDE(" HU","csak",4)="" ^dc.test2.iDE(" HU","cukor",6)="" ^dc.test2.iDE(" RU","Павел",8)="" ^dc.test2.iDE(" RU","Прохор",7)="" ^dc.test2.iDE(" RU","Пётр",9)="" ^dc.test2.iHU(" DE","Strasser",3)="" ^dc.test2.iHU(" DE","Straster",2)="" ^dc.test2.iHU(" DE","Straßer",1)="" ^dc.test2.iHU(" HU","comb",5)="" ^dc.test2.iHU(" HU","cukor",6)="" ^dc.test2.iHU(" HU","csak",4)="" ^dc.test2.iHU(" RU","Павел",8)="" ^dc.test2.iHU(" RU","Прохор",7)="" ^dc.test2.iHU(" RU","Пётр",9)="" ^dc.test2.iRU(" DE","Strasser",3)="" ^dc.test2.iRU(" DE","Straster",2)="" ^dc.test2.iRU(" DE","Straßer",1)="" ^dc.test2.iRU(" HU","comb",5)="" ^dc.test2.iRU(" HU","csak",4)="" ^dc.test2.iRU(" HU","cukor",6)="" ^dc.test2.iRU(" RU","Павел",8)="" ^dc.test2.iRU(" RU","Пётр",9)="" ^dc.test2.iRU(" RU","Прохор",7)=""ID Descr Lng 3 Strasser DE 1 Straßer DE 2 Straster DE
3 Rows(s) Affected
ID Descr Lng 5 comb HU 6 cukor HU 4 csak HU
3 Rows(s) Affected
ID Descr Lng 8 Павел RU 9 Пётр RU 7 Прохор RU
3 Rows(s) Affected
Important: if you remove "As EXACT" in the index, the examples will not work correctly for the German language, because: Capital ẞ