Written by

Question Stefan Schick · Jun 12, 2024

CREATE TABLE AS SELECT fails when one specific field is present

#InterSystems IRIS for Health #InterSystems IRIS #Databases #SQL

Hello Community,

I have encountered the following SQL problem trying to create a table from a SELECT statement.

I narrowed the problem down to the following example:

1. The SELECT statement alone works as expected.

SELECT m.Name, h.Status 
FROM EnsLib_HL7.Message m, Ens.MessageHeader h 
WHERE h.MessageBodyClassName='EnsLib.HL7.Message' AND h.MessageBodyID = m.ID

2. But if "CREATE TABLE ... AS" is added it fails

CREATE TABLE UKEr.Test AS
SELECT m.Name, h.Status
FROM EnsLib_HL7.Message m, Ens.MessageHeader h
WHERE h.MessageBodyClassName='EnsLib.HL7.Message' AND h.MessageBodyID = m.ID

with the error message

  [SQLCODE: <-400>:<Fatal error occurred>]


  

[%msg: <SQLCODE: -400 Message: Process 73824 failed to compile Cached Query Class %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8 with the following errors: System:Instance: svm-ap-ekds1q:DEVHS, Namespace: UKERDFT, $Job: 73824 ERROR #5475: Error compiling routine: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8. Errors: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.cls ERROR: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.1(57) : MPP5610 : Referenced macro not defined: 'vaExtentGbl' TEXT: . try { Set segid=$G($$$vaExtentGbl(i%ID,"segs",1),"0,0"), data=$S(segid[",":$G($$$vaSegment(segid)),1:$G($$$vaSegmentGbl(segid))), i%Name=$Case($$$vaDataSegName(data),"FHS":"FHS","BHS":"BHS",:##class(EnsLib.HL7.Message).getDataName(data)) ERROR: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.1(74) : MPP5610 : Referenced macro not defined: 'vaExtentGbl' TEXT: . try { Set segid=$G($$$vaExtentGbl(i%ID,"segs",1),"0,0"), data=$S(segid[",":$G($$$vaSegment(segid)),1:$G($$$vaSegmentGbl(segid))), i%Name=$Case($$$vaDataSegName(data),"FHS":"FHS","BHS":"BHS",:##class(EnsLib.HL7.Message).getDataName(data)) ERROR #5475: Error compiling routine: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.1. Errors: ERROR: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.1.int(76) #1002: Invalid character in tag : 'segid=$G($$$vaExtentGbl(i%ID,"segs",1),"0,0"),' : Offset:25 [%0AmBuncommitted+5^%sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.1] > ERROR #5030: An error occurred while compiling class '%sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8' ERROR #5475: Error compiling routine: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.1. Errors: ERROR: %sqlcq.UKERDFT.xE0CXKiKodboVaHSFfXamhsFi3m8.1.int(89) #1002: Invalid character in tag : 'segid=$G($$$vaExtentGbl(i%ID,"segs",1),"0,0"),' : Offset:25>]

3. In contrast the following statement works fine. The only difference is that the field "m.Name" is not used. For demonstation purposes replaced with a different field from the same table "m.DocType". Other fields also work.

CREATE TABLE UKEr.Test AS
SELECT m.DocType, h.Status
FROM EnsLib_HL7.Message m, Ens.MessageHeader h
WHERE h.MessageBodyClassName='EnsLib.HL7.Message' AND h.MessageBodyID = m.ID

It seems to me, the field "Name" of the Table "EnsLib_HL7.Message" causes the trouble but I don't know why (maybe it interferes with a reserved word?) and I couldn't figure out a way to avoid this problem.

Has someone any idea what's going on here and how to solve this problem?

Kind Regards
Stefan

Product version: IRIS 2021.1

$ZV: IRIS for UNIX (SUSE Linux Enterprise Server for x86-64) 2021.1 (Build 215U) Wed Jun 9 2021 09:48:27 EDT [HealthConnect:3.3.0] [HealthConnect:3.3.0]

Discussion (4)2

Add reply

Comments

Robert Cemper · Jun 12, 2024

I share your suspicion on reserved Verbs.

You may try

m.Name as "myName"

Or even

m.Name as "Name"

Just a guess from the hip

0 0

Stefan Schick Jun 12, 2024 to Robert Cemper

Hi Robert,

thanks for your "guess" but I tried this already - unfortunately it doesn't work.

I also tried creating a view with a different name for that field - didn't work either.

Regards

Stefan

0 0

Jeffrey Drumm Jun 12, 2024 to Stefan Schick

Odd. I just ran your exact query on IRIS for Health 2023.3 and it executed successfully.

The error message suggests that a macro isn't defined, specifically $$$vaDataSegName, which is found in EnsHL7.inc (among others). Since you're working specifically with HL7 messages I suspect that include file is not available to the namespace in which you're running the query.

0 0

Stefan Schick Jun 13, 2024 to Jeffrey Drumm

Hi Jeffrey,

thanks for checking! Glad to see that this might be fixed with our next update!

The cause of the problem appears to lie in the computed fields of the table, the macros used might not work in a different table/class. Other computed fields ("Name", "Identifier", "TypeVersion") don't work either.

Meanwhile I found a workaround:

Instead of using the computed field "Name" it's contents can be computed explicitely. It's not 100% the same, because some details, eg taking into accout the separator characters, are missing but it works for me!

CREATE TABLE UKEr.Test AS
SELECT REPLACE($EXTRACT($PIECE(m.RawContent,'|',9),1,7),'^','_') Name, h.Status
FROM EnsLib_HL7.Message m, Ens.MessageHeader h
WHERE h.MessageBodyClassName='EnsLib.HL7.Message' AND h.MessageBodyID = m.ID

Regards
Stefan

0 0