Details
Description
In the metadata tables, concepts can be setup as a multiple in which a single concept will automatically include other concepts when a query is run. The concepts to be included are defined in the C_DIMCODE column.
The problem is when there is a space between the values entered in the C_DIMCODE the single quotes are not being escaped properly when the SQL is generated. This is resulting in the incorrect number of patients being returned.
If you remove the spaces between values the single quotes are being escaped correctly.
The following two examples were done using the i2b2 demo data.
EXAMPLE 1: Spaces in the C_DIMCODE
C_FULLNAME = \i2b2\Demographics\Race\Black\
C_DIMCODE = 'b', 'black', 'hib', 'his/black'
Excerpt of SQL Generated =
RACE_CD IN ('b'', ''black'', ''hib'', ''his/black')
EXAMPLE 2: No spaces in the C_DIMCODE
C_FULLNAME = \i2b2\Demographics\Race\Black\
C_DIMCODE = 'b','black','hib','his/black'
Excerpt of SQL Generated =
RACE_CD IN ('b','black','hib','his/black')
WHAT SHOULD HAPPEN:
Regardless of whether or not there are spaces between the values, the generated SQL should escape the single quotes correctly.
The problem is when there is a space between the values entered in the C_DIMCODE the single quotes are not being escaped properly when the SQL is generated. This is resulting in the incorrect number of patients being returned.
If you remove the spaces between values the single quotes are being escaped correctly.
The following two examples were done using the i2b2 demo data.
EXAMPLE 1: Spaces in the C_DIMCODE
C_FULLNAME = \i2b2\Demographics\Race\Black\
C_DIMCODE = 'b', 'black', 'hib', 'his/black'
Excerpt of SQL Generated =
RACE_CD IN ('b'', ''black'', ''hib'', ''his/black')
EXAMPLE 2: No spaces in the C_DIMCODE
C_FULLNAME = \i2b2\Demographics\Race\Black\
C_DIMCODE = 'b','black','hib','his/black'
Excerpt of SQL Generated =
RACE_CD IN ('b','black','hib','his/black')
WHAT SHOULD HAPPEN:
Regardless of whether or not there are spaces between the values, the generated SQL should escape the single quotes correctly.