New post
AllAccepted answers
Benjamin De Boe · Mar 23, 2021

Thanks for prodding me on this. It's a perfect opportunity to advertise an upcoming feature that addresses this exact problem.

SQL collations have existed for a long while, but as Robert pointed out, were restricted to playing with upper/lower case, truncation, whitespace and the odd numeric ploy through a limited set of built-in collations. Later this year, we'll release a more generic %COLLATE() option that allows you to combine these properties any way you like, including a translation that removes all accents (both as a SQL collation option and a new $zconvert() option).

In the meantime, you can use the somewhat impractical workarounds described above, or possibly use an iFind index and specify a TRANSFORMATIONSPEC that removes the accents.

Benjamin De Boe · Feb 9, 2021

Yes it does. I don't have time to turn this into an easy zpm-compatible module right away, but the following commands worked fine for me on IRIS, after cloning the github repo:

do $system.OBJ.ImportDir("/path/to/downloaded/isc-iknow-setanalysis","*.xml","c",,1)

do ##class(Demo.SetAnalysis.Utils).CreateRestWebApp()

The first command may throw a few errors for some BI-related things that no longer seem to work out-of-the-box, but the core app works just fine after running the above two lines. You can access it the URL described in the article above.

Benjamin De Boe · Jan 28, 2021

IRIS will automatically create an auto-incrementing RowID and leverage it as the IdKey for you, so unless you want anything other than the default, you shouldn't define such a field or index explicitly. 

If all you want is control the name, take a look at the SqlRowIdName class parameter. If you need control over its behaviour, what you've set up is appropriate and you can leverage SqlRowIdPrivate to get rid of the default additional projection of the RowID. However, unless there's a good reason for controlling the IdKey, you should try to avoid overriding it as it may deprive you of certain practical features and efficiencies such as bitmap indices and an extent index.

Benjamin De Boe · Jan 5, 2021
  • It does not seem to be aware of the index so index parameters would be missed as only INDEXOPTION can be passed.

hmm, that shouldn't happen. if you could file a reproducible test case through our internal systems, we'll take a look. Or perhaps you didn't use the implicitly-generated [package name].[table name]_[index name]Highlight() procedure?

Benjamin De Boe · Jan 5, 2021

You can expose this information through setting the IFINDMAPPINGS parameter to 1:

  • [class_name]_[index_name]_WordRec: stores which words appear in each record in this index. See also %iFind.Index.AbstractWordRec.
  • [class_name]_[index_name]_WordSpread: stores the total number of records in which this word appears in this index. See also %iFind.Index.AbstractWordSpread.
  • [class_name]_[index_name]_WordPos stores which word occurs at which position in a record, so it can be joined to the AttributePos table. See also

So by doing a COUNT() on the WordPos table, you should find what you're looking for IFF it corresponds to an actual word. If you're using wildcards, you might combine with %iFind.FindWords() as a TVF, but that'd still be looking for individual words only:

SELECT COUNT(*) 
FROM Test_IFind.IFind_IF_WordPos 
WHERE WordId IN (SELECT WordId FROM %iFind.FindWords('ab*'))

If you want to count any kind of match, your highlight trick is probably the nicest way to get at it.

(as an aside: with the introduction of the %iFind.Index.Minimal index type in 2020.1, which BTW was Eduard's suggestion ;-), it seems the class reference for the IFINDMAPPINGS projections added by the .Basic class but not in .Minimal got lost. We'll fix that shortly!)

Benjamin De Boe · Nov 10, 2020

Thanks for the reference. That's indeed a very good approach to solve the international-exact-sort question through NLS collations (see also this note). The new SQL collation described above is meant to provide an easy way to have an international-broad-brush transformation to accommodate the non-exact cases, such as using a LIKE operator that doesn't trip over a single-accent difference.

Benjamin De Boe · Nov 3, 2020

UPDATE: We uploaded an updated version of our 2020.4 preview release, including a small number of additional enhancements in the broader SQL area that missed the boat for the scheduled preview release date. These features meet the quality requirements for inclusion in the GA release later this month so we thought it was worthwhile sharing them in preview mode for users to try ahead of time.

The new build number is 524, up from 521. The new images have been posted on the ICR, Docker Store and WRC locations as described above and the docker pull scripts have been updated.

As always, we're looking forward to your feedback!

Benjamin De Boe · Oct 21, 2020

Nice article. Very much looking forward to your views on %SYSTEM.WorkMgr, which has been getting a lot of attention to help us serve the most demanding SQL workloads from our customers. You'll also be able to monitor some of its key metrics such as active worker jobs and average queue length in SAM starting with 2021.1.

Benjamin De Boe · Oct 12, 2020

Robert is correct, you can leverage our TSQL support to mimic that INSERT SELECT model Microsoft and Sybase offer. I would not recommend mixing TSQL with plain SQL on an ongoing basis to build your non-TSQL application, but just using the command on a one-off basis (it's a temp table you're after anyhow) should be just fine.

in IRIS SQL (and most other databases), INSERT SELECT will just do that, insert based on the selection, and not implicitly create the table if it doesn't exist. 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 :-)

Benjamin De Boe · Sep 11, 2020

Hi Mark,

can you be more specific on the nature of the data and possibly application that's going to be migrated? HealthShare is more a suite of applications with standardized data models underneath, running on top of InterSystems IRIS for Health. To ingest data into HealthShare means transforming it through its APIs, whereas IRIS for Health is more comparable to a relational database like DB2.

SQL Gateways allow mapping tables in remote non-InterSystems databases (such as DB2) to IRIS so they can be accessed as if they were local. That can be helpful during migrations as well as heterogeneous production environments. TSQL is a specific set of extensions of the SQL language used by Sybase and MS SQL Server that we support to ease migration scenarios for new customers with applications written in TSQL.

hope this helps,
benjamin

Benjamin De Boe · Sep 10, 2020

Hi Mohamed,

there is no reason to start any new projects with InterSystems Caché. It's a platform that has proven itself for numerous years, but it's now being superseded by InterSystems IRIS, which builds on the same core strengths but has also been bolstered by pretty much all the new development at InterSystems over the past three years. 

This said, to complement Kevin's excellent list of links, you can also check out https://www.intersystems.com/try for a free trial hosted by us (so you won't need your cloud account just yet) and https://gettingstarted.intersystems.com/ with more entry-level documentation and tutorials.

thanks,
benjamin