Migrating to IRIS changes

Question

Question

Rochdi Badis · May 9, 2024

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

Product version: IRIS 2023.3

Discussion (5)1

Log in or sign up to continue

Robert Cemper · May 9, 2024

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

0 0

Timo Lindenschmid · May 9, 2024

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.

1 0

score 0 · Answer 1 · 2024-05-09T05:22:57-04:00

Rochdi Badis · May 9, 2024

Thanks Robert for the tips they were very helpful.

Cheers

0 0

score 0 · Answer 2 · 2024-05-09T03:54:59-04:00

some decade+ back there was a decision that SQL should be identic
whether embedded, in SMP, ODBC, SQL Shell, or SQL.Statement.

score 0 · Answer 3 · 2024-05-09T05:21:12-04:00

Rochdi Badis · May 9, 2024

Yes single quote worked
Thanks

0 0