· Oct 28, 2023

escape bracket in SQL

I've written a stored procedure for SQL as follows. The second parameter is a search path that may use a single quote or bracket as part of its expression.

When the expression uses a single quote, I can double it within SQL, and it works well. In this example, 'testDate' is written as ' ' testDate ' ' .


Select HISOL_MEAS.SQLProc_JSONpath(fullAnnotation,'HbA1cTests.sort(''testDate'',false).resultValue') As reverseSortedTests


In the next example I'd like to use brackets (see below), which causes an error.  

Trying to escape the bracket by doubling it still produces an error.  Any suggestions how I might accomplish this?


'HbA1cTests.sort(''testDate'',false)[0].resultValue' As recentHbA1c

'HbA1cTests.sort(''testDate'',false)[[0]].resultValue' As recentHbA1c

Product version: IRIS 2022.1
$ZV: 2022.1.2 (Build 574_0_22407U)
Discussion (5)1
Log in or sign up to continue


which error ??   pls. be specific with all details

hint for SQL Escaping

but test in SQL Shell shows no problem

USER>do $system-.SQL.Shell()
SQL Command Line Shell
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>select 'HbA1cTests.sort(''testDate'',false)[0].resultValue'
1.      select 'HbA1cTests.sort(''testDate'',false)[0].resultValue'
1 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0002s/5/166/0ms
          execute time(s)/globals/lines/disk: 0.0002s/0/385/0ms
                          cached query class: %sqlcq.USER.cls117

hi robert - - i think I found the issue.....

the problem is not in the use of the bracket, but rather, that an empty set (taking the 0th element of the empty set) is leading to the error.  

If I add to the where class, 'where runid='201' and HbA1cMissing not like '1', I don't see an error.

Thank you for prompting me to take a closer look at the error.