July 9, 2018 at 11:30 pm
Hi Team,
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" a9WHERE
(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
July 10, 2018 at 8:29 am
Your query doesn't have any inherently dynamic columns in it, so I have to GUESS that you are using a presentation or reporting tool that does some kind of pivot on your data. You would have to adjust that query in some way, based on the nature of that pivoting, to guarantee that the "test1" value appears (probably involving UNION at some point, but again, very much a total guess). You'll need to provide a LOT more detail in order for anyone to have much of a shot at helping you, and it would need to include CREATE TABLE statements for ALL the tables in your query, along with INSERT statements for sample data that can demonstrate your problem condition, along with a way to make that condition disappear just by virtue of a change in the INSERTed data. We'd also need to know EXACTLY which presentation / reporting tool you are using along with exactly how it is being configured / used.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply