SQLTeam.com | Weblogs | Forums

Sql not returning columns metadata when the actual values are null


#1

I have an issue in information link sql query which joins multiple tables where some dynamic column names are returned.
They are stored in "label" and "value" attribute of the below query.
Please note we had certain fields in the database which when null doesn't appear as column in the output.
For e.g a field "test1" in the database has all values null then "test1" doesn't get returned.
However, even if a single value (row) of "test1" has some data, I see "test1" in the output.
The requirement is always have this field in the output irrespective of whether it has the data for not.
Please see the query below and point out what is causing issue. Thanks for your help in advance.

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')

Regards,
Mandeep


#2

This doesn't look like T-SQL. in particular, left(?ver, 7) is not T-SQL AFAIK


#3

Actually, this is information link Spotfire Query which converts to T-Sql as the DB is Sql Server.


#4

Can you post the T-sql converted version?