EndsWith error

I think I have reported this before, but I can't find it in my archive. EndsWith() is incorrectly translated to Oracle; it will return false if the argument is found in the string anywhere that isn't the end (or if it is not found at all, of course). Any suggestions on how to redefine it correctly?

EndsWith Translation

Hi Jon,

The algorithm seems right, but it could be the semantics of the Length operator in Oracle. I don't have an Oracle instance set up to test. Can you provide the D4ToSQL output for the expression that is failing?

Bryn Rhodes
Database Consulting Group LLC

EndsWith Translation

select D4ToSQL('School { SchoolName } where SchoolName.EndsWith("s")'):

select /*+ FIRST_ROWS(20) */
T1.SchoolName as SchoolName
from forskdb.School T1
where (case when Instr(T1.SchoolName, 's') = (Length(T1.SchoolName) - Length('s') + 1) then 1 else 0 end = 1)

The case expression correctly gives 1 for school names like "Ytre Namdal vgs", but incorrectly 0 for names like "Leksvik vgs", where the substring appears not only at the end, but also earlier. Oracle's Instr function returns the index of the first substring appearance, not the last.

It's easy enough to fix the expression, but how can I change the operator map? I tried alter device MyOracleDevice { alter operator EndsWith(String, String) alter tags { Storage.TranslationString = "substr({0}, length({0}) - length({1}) + 1) = {1}" } }, but that didn't seem to have any effect. How can I view operator maps in Dataphoria?

EndsWith mapping

The Instr has a start pos argument, and if that is negative, it will search from the beginning of the string. I thought that was being used, but I must have been looking at the LastPos translation string. Apologies for the confusion. At any rate, you should be able to replace the mapping, but the alter will probably not work. Try dropping and then recreating the mapping.

Here is how you would find the current mapping:

// Use the DeviceOperators table to get the name of the mapping:
select DeviceOperators;

// Then use the script operator to get the D4 to recreate the mapping.
select Script('Sample.Pubs.Pubs_MSSQLDevice.iCompare0000011993_Map0000012497');

And if all this fails, the LastPos operator does appear to be mapped correctly, so a workaround would be to express it in D4 as LastPos = Length.

Hope this helps,
Bryn Rhodes
Database Consulting Group LLC