提问人：vascobnunes 提问时间：9/20/2022 最后编辑：marc_svascobnunes 更新时间：9/17/2023 访问量：110
报表中表格中的 <memo> 字段缺少文本
Missing text from <memo> field in table in report
问：
我在Sparx Enterprise Architect中有多个基于文件的模型，即使用MS Access。
我正在使用自定义模板使用对象属性中的数据填充表，包括一些带有字段的数据。<memo>
这是我在模板片段中使用的查询：
SELECT
obj.object_id,
obj.Stereotype,
objp.Property as Prop,
switch(objp.Value = '<memo>', objp.Notes, objp.Value LIKE '{*}',
NULL, 1=1, objp.Value) AS Val,
(SELECT tobj2.ea_guid & tobj2.Name
FROM t_object tobj2
WHERE tobj2.ea_guid = objp.Value) AS [Obj-Hyperlink]
FROM
t_object obj
INNER JOIN
t_objectproperties objp ON (obj.object_id = objp.object_id)
WHERE
obj.object_id = #OBJECTID#
AND obj.Stereotype = 'Data-Stream'
AND objp.Property NOT IN ('isEncapsulated')
ORDER BY
objp.Property ASC;
我发现当这些字段长度超过 249 个字符时，我在生成报告时收到一条错误消息，并且生成的表中的单元格只是空的。这在查询中也很明显：
这是我收到的错误：
处理 xml 文档时出错：在文本上下文中发现无效字符”
是否有任何解决方法可以在报告中包含超过 249 个字符的字段数据？<memo>
非常感谢任何帮助。
答：
1赞
vascobnunes
9/22/2022
#1
我通过将两个查询与“全部联合”连接起来，找到了解决方法。第一个查询将使用 switch 函数处理非备忘录字段，第二个查询将处理不带 switch 函数的备忘录字段。
select
obj.object_id,
obj.Stereotype,
objp.Property as Prop,
objp.Notes AS Val,
(
SELECT
tobj2.ea_guid & tobj2.Name
FROM
t_object tobj2
WHERE
tobj2.ea_guid = objp.Value
) AS [Obj-Hyperlink]
from
t_objectproperties objp
left join t_object obj on (obj.object_id = objp.object_ID)
where
obj.object_id = #OBJECTID#
AND obj.Stereotype = 'Data-Stream'
AND objp.Property NOT IN ('isEncapsulated')
AND objp.Value = "<memo>"
UNION ALL
SELECT
obj2.object_id,
obj2.Stereotype,
objp2.Property as Prop,
switch(
objp2.Value LIKE '{*}', NULL, 1 = 1, objp2.Value
) AS Val,
(
SELECT
tobj2.ea_guid & tobj2.Name
FROM
t_object tobj2
WHERE
tobj2.ea_guid = objp2.Value
) AS [Obj-Hyperlink]
FROM
t_object obj2
INNER JOIN t_objectproperties objp2 ON (obj2.object_id = objp2.object_id)
WHERE
obj2.object_id = #OBJECTID#
AND obj2.Stereotype = 'Data-Stream'
AND objp2.Property NOT IN ('isEncapsulated')
and objp2.Value <> "<memo>"
order by
3 asc;
非常感谢@geertbellekens的评论，这对于找到这个解决方案至关重要。
评论