Category Archives: SQL Coding

Tips and Techniques for writing SQL in Sql Server

Sql Server Self-Join Child to Parent Hierarchy

Problem:   Series of child-parent relationship rows where you need to find the topmost parent.

Solution: TBD!

Script to create the situation below…

USE TempDb
GO
— Create a Table
drop table party_merge
go
CREATE TABLE Party_merge(
FromPartyID INT PRIMARY KEY,
ToPartyID INT
)
GO
— Insert Sample Data
— 3 gets gets 4 children (1, 2, 8, 9) assigned to merge to it and then itself gets merged to id=10
— 2 is the new parent for 4, 5, and 7
— 11 gets 12 assigned. Only parent 12 has only one child (11)

— Note:
— 14 gets parent assigned as 14 (due to source system issue)

INSERT INTO Party_merge
SELECT 1, 3
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 14, 14
UNION ALL
SELECT 4, 2
UNION ALL
SELECT 5, 2
UNION ALL
SELECT 7, 2
UNION ALL
SELECT 8, 3
UNION ALL
SELECT 9, 3
UNION ALL
SELECT 3, 10
UNION ALL
SELECT 11, 12
GO
— Check the data

SELECT *
FROM Party_merge
ORDER BY FromPartyID, ToPartyID
GO

—————————————————–
SELECT p1.FromPartyID, p1.ToPartyId
FROM Party_merge p1
LEFT OUTER JOIN Party_merge p2
ON p1.ToPartyID = p2.FromPartyID
WHERE p1.FromPartyID <> p1.ToPartyID
———————————–

Sql Server – When ISNULL results in “Insufficient result space to convert” …

This is a common error when doing null handling when replacing incoming value with something larger since the datatype defaults based on the incoming value.

You need to not just CAST or CONVERT to a different larger datatype but once but you have do it twice – both on the incoming column and then on the result. This can also happen when doing aggregations as well as it may demand overriding the default decimal or integer size for the resulting value.

Below demonstrates a further complication where CAST doesn’t work but CONVERT does…

Following doesn’t work…
SELECT CAST(ISNULL(CAST([Person_Acxiom_Abilitec_Id] AS VARCHAR(36)), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id…

Following does…
CAST(ISNULL(CONVERT(VARCHAR(36),[Person_Acxiom_Abilitec_Id]), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id

The only difference is using CONVERT instead of CAST.