New post
Question
· Jul 13, 2018

Is there a way to define a custon collation for SQL table properties?

#SQL #Caché

I would linke to have the functionalities provided by the Table Field/Property Collation, but applying a transformation which differs from the provided ones.

Does anyone know if there is a way do accomplish that?

Discussion (5)1
Log in or sign up to continue
Robert Cemper · Jul 13, 2018

I'm not aware of a "customized" collation.

But this might be an appropriate workaround:

- for your property, you define an additional calculated property that results out of your  customized collation
- for this new calculated property,  you define COLLATION = EXACT to avoid default surprises (SQLUPPER !!!)

If you index it, you should get what you expected without impact to the rest of your table

0 0
Fernández · Jul 16, 2018

Thank you for your answer Robert.

This was my initial approach as well. The problem is that you need to use the calculated property to filter and ordering, but the original one to get the value. This will be confusing for the development, and may lead to some issues in the future.

This is why I thought about COLLATION, because it provides the desired behavior, I just need a custom transformation :)

0 0
Fernández · Jul 17, 2018

The default collation for strings is SQLUPPER, which allows non-case-sensitive searches. So assuming the following table, named foo:

IDbar
1 Hello
2 World

All following sql queries will return exactly the same value in myBar, which will be exactly Hello.

SELECT bar As myBar FROM foo where bar = 'HELLO'

SELECT bar As myBar FROM foo where bar = 'hello'

SELECT bar As myBar FROM foo where bar = 'heLLo'

What I need is something very similar but ignoring leading zeroes when searching.

IDbar
1 00123AB
2 12345AB

So the following two queries shall return 00123AB as myBar.

SELECT bar As myBar FROM foo where bar = '00123AB'

SELECT bar As myBar FROM foo where bar = '123AB'

As you can see, the desired behavior is the same, just different transformation. Whether there are few to none use cases where applying this transformation makes sense is another discussion.

Normally I would implement this behavior in the business logic, not in the data model. But this is a change that we need to implement in a really legacy part with a lot of existing business logic. So having the possibility of implementing it that way would save a LOT of effort.

I don't see how I can get this behavior by overriding the methods DisplayToLogical/LogicalToDisplay and/or LogicalToStorage/StorageToLogical.

0 0
Vitaliy Serdtsev · Jul 17, 2018

Use %MVR

For example:

Class dc.test Extends %Persistent
{

Index ibar On bar;

Property bar As %String(COLLATION = "MVR");

ClassMethod Test()
{
  d ..%KillExtent()
  &sql(insert into dc.test(bar)
  select '00123AB' union
  select '12345AB')

  d $system.SQL.TuneTable($classname(),$$$YES), $system.OBJ.Compile($classname(),"cu-d")
  f args="00123AB","123AB" d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.test where bar=?",args).%Display() w !!
}

}

Result:

USER>d ##class(dc.test).Test()
ID      bar
1       00123AB
 
1 Rows(s) Affected
 
ID      bar
1       00123AB
 
1 Rows(s) Affected
0 0