Why Does This Query Return Blank Columns? - RESOLVED

Transact-SQL
1

Hi experts,

For some reason, after adding Shipments.ShipperOrgId AS 'td', Shipments.ConsigneeOrgID AS 'td'
those 2 columns return blank/nukll values. The data in the table is NOT blank/null. How can this happen? Thanks

SET @xml = CAST((SELECT Orgs.Name AS 'td', '', Shipments.ProNumber AS 'td', '', Count( Shipments.Id) AS 'td', '', Shipments.Status AS 'td', '', Orgs_1.Name AS 'td', '', Shipments.ShipDT AS 'td', Shipments.ShipperOrgId AS 'td', Shipments.ConsigneeOrgID AS 'td'
FROM ( Shipments INNER JOIN Orgs ON Shipments.ParentOrgId = Orgs.Id) INNER JOIN Orgs AS Orgs_1 ON Shipments.CarrierOrgId = Orgs_1.Id
GROUP BY Orgs.Name, Shipments.ProNumber, Shipments.Status, Orgs_1.Name, Shipments.ShipDT, Shipments.ParentOrgId, Shipments.ShipperOrgId, Shipments.ConsigneeOrgId
HAVING (((Shipments.ProNumber) Is Not Null And (Shipments.ProNumber) <>' ' And (Shipments.ProNumber) Not Like ('%0000%') And LEN(Shipments.ProNumber) > 3 AND ((Count(Shipments.Id))>1) AND ((Shipments.Status)<>3) AND ((Shipments.ShipDT)>=@180DaysAgo) AND ((Shipments.ParentOrgId)<>131)))
ORDER BY Count(Shipments.Id) DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

2

Resolved.

3

please share what the resolve was

4

SET @xml = CAST((SELECT Orgs.Name AS 'td', '', Shipments.ProNumber AS 'td', '', Count( Shipments.Id) AS 'td', '', Shipments.Status AS 'td', '', Orgs_1.Name AS 'td', '', Shipments.ShipDT AS 'td', '', Shipments.ShipperOrgId AS 'td', '', Shipments.ConsigneeOrgID AS 'td'
FROM ( Shipments INNER JOIN Orgs ON Shipments.ParentOrgId = Orgs.Id) INNER JOIN Orgs AS Orgs_1 ON Shipments.CarrierOrgId = Orgs_1.Id
GROUP BY Orgs.Name, Shipments.ProNumber, Shipments.Status, Orgs_1.Name, Shipments.ShipDT, Shipments.ParentOrgId, Shipments.ShipperOrgId, Shipments.ConsigneeOrgId
HAVING (((Shipments.ProNumber) Is Not Null And (Shipments.ProNumber) <>' ' And (Shipments.ProNumber) Not Like ('%0000%') And LEN(Shipments.ProNumber) > 3 AND ((Count(Shipments.Id))>1) AND ((Shipments.Status)<>3) AND ((Shipments.ShipDT)>=@180DaysAgo) AND ((Shipments.ParentOrgId)<>131)))
ORDER BY Count(Shipments.Id) DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

5

Which does not help. just a blob of text.

SET @xml = CAST((SELECT o.Name AS 'td', 
                        '', s.ProNumber AS 'td',
						 '', Count( Shipments.Id) AS 'td', 
						 '', s.Status AS 'td', 
						 '', Orgs_1.Name AS 'td', 
						 '', s.ShipDT AS 'td', 
						 '', s.ShipperOrgId AS 'td', 
						 '', s.ConsigneeOrgID AS 'td'
                  FROM ( Shipments s
				 INNER JOIN Orgs o
				    ON s.ParentOrgId = o.Id) 
				 INNER JOIN Orgs AS Orgs_1 
				 ON s.CarrierOrgId = Orgs_1.Id
                 GROUP BY o.Name, 
                          s.ProNumber, 
                          s.Status, 
						  Orgs_1.Name,
                          s.ShipDT, 
                          s.ParentOrgId, 
                          s.ShipperOrgId,
                          s.ConsigneeOrgId
                HAVING (((s.ProNumber) Is Not Null 
				      And (s.ProNumber) <>' ' 
					  And (s.ProNumber) Not Like ('%0000%') 
					  And LEN(s.ProNumber) > 3 
					  AND ((Count(s.Id))>1) 
					  AND ((s.Status)<>3) 
					  AND ((s.ShipDT)>=@180DaysAgo) 
					  AND ((s.ParentOrgId)<>131)))
                 ORDER BY Count(s.Id) DESC
                   FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));
1 Like