时间戳

首页 > 解决方案 > 当实际值为空时，Sql 不返回列元数据

sql - 当实际值为空时，Sql 不返回列元数据

问题描述

我在信息链接 SQL 查询中遇到问题，该查询连接了返回一些动态列名的多个表。它们存储在以下查询的属性中label。value请注意，我们在数据库中有某些字段，当 null 不作为列出现在输出中时。例如test1，数据库中的字段具有所有值null，然后test1不会返回。但是，即使单个值（行）test1有一些数据，我也会test1在输出中看到。要求始终在输出中具有此字段，而不管它是否具有数据。请参阅下面的查询并指出导致问题的原因。

SELECT
   I1."item_uid" AS "ITEMUID",
   I1."item_reference_uid" AS "ITEMREFERENCEUID",
   w2."workflow_abbrv" AS "STATUS",
   s3."label" AS "LABEL",
   s3."value" AS "VALUE",
   a4."active_status_desc" AS "ACTIVESTATUSDESC",
   I5."item_reference_uid" AS "PARENTID",
   I5."item_desc_display" AS "PARENTDESCDISPLAY"
FROM
   ("synaptica"."dbo"."sub_elements" s3 LEFT OUTER JOIN "synaptica"."dbo"."ITEMS_REPOSITORY" I1 ON I1."item_uid" = s3."item_uid") 
   LEFT OUTER JOIN("synaptica"."dbo"."ITEMS_REPOSITORY" I5 
   RIGHT OUTER JOIN(select "syndetic_key_item", "rel_type_abbrv", "syndetic_rel_item" from "synaptica"."dbo"."SYNDETIC_NETWORK" s55
   left join "synaptica"."dbo"."RELATIONSHIP_TYPES" R66 ON S55."syndetic_rel_type" = R66."rel_type_uid"
    where (R66."rel_type_abbrv" = 'PhysParent' or R66."rel_type_abbrv" is null))S6

ON 

I5."item_uid" = S6."syndetic_rel_item") ON I1."item_uid" = S6."syndetic_key_item",
   "synaptica"."dbo"."workflow_types" w2,
   "synaptica"."dbo"."active_statuses" a4,
   "synaptica"."dbo"."OBJECT_CLASSES" O8,
   "synaptica"."dbo"."approval_statuses" a9

WHERE
   (I1."item_active_status" = a4."active_status_uid")
   AND (a9."approval_status_uid" = I1."item_approval_status")
   AND (I1."item_object_class" = O8."object_class_uid")
   AND (I1."item_workflow" = w2."workflow_uid")
   AND ((left(O8."object_class_abbrv",
   7) = left(?VER,
   7)))
   AND (s3."label" <> 'CLS360_OBJECT_ID')
   AND (RIGHT(O8."object_class_abbrv",
   10) = 'PHYS CLASS')

标签： sqlsql-serverdynamic-columns

解决方案

推荐阅读