Migrating to IRIS changes
Hi Guys,
We are migrating from Ensemble 2018 to the latest IRIS, and while compiling we get many of these errors in our queries:
and this a sample of one our queries:
&sql(select CASE WHEN StarterKitName != "" THEN Kit->Name || ' - ' || StarterKitName ELSE Kit->Name END into :KitName from MSDS_Serenity.KitLabelAssoc where Label = :Label and Status = 1
I've also heard that the &sql syntax also has changed, I've download the How to Migrate to InterSystems IRIS but couldn't find changes related to the above?
Thanks
Comments
your CASE statement is wrong in multiple ways:
and doesn't work in ENSEMBLE /Caché either
- double quotes " mark column names, strings require single quotes '
- !="" is an Objectscript construct
- you have to write NOT IS NULL instead
select CASE WHEN StarterKitName NOT IS NULL THEN ....See also example in ENS/Caché docu :
https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_case
Thanks Robert for the tips they were very helpful.
Cheers
Hi,
if i remember correctly, there was a breaking change that requires the use of single quotes in SQL as a string delimiter. Double quotes are only allowed to be used for field/table names that use special chars etc.
some decade+ back there was a decision that SQL should be identic
whether embedded, in SMP, ODBC, SQL Shell, or SQL.Statement.
Yes single quote worked
Thanks