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