function to get the city details of the parent

Question

function to get the city details of the parent

Shanmuga Raj

Ten Centuries

Points: 1178
More actions
January 19, 2014 at 12:54 pm

#287388

I have an existing function and need to alter function to give result of the values parent-description until its parent is reached.
--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]
---INSERT DATA
insert into CityData
values(1,-1,'Bangalore','BangaloreMain',20),
(2,1,'NULL','North Bangalore',10),
(3,2,'NULL','North Bangalore - Area-1',5),
(4,2,'NULL','North Bangalore - Area-2',15),
(5,1,'NULL','South Bangalore',7),
(6,-1,'Others','Coimbatore',4),
(7,6,'NULL','Coimbatore South',5),
(8,7,'NULL','Tirupur',1),
(9,7,'NULL','Avinashi',3)
----CREATE FUNCTION
CREATE FUNCTION [dbo].[GetFirstLevelLabel]
(
@cityParentId int
)
RETURNS nvarchar(128)
AS
BEGIN
DECLARE @strLabel nvarchar(128)
set @strLabel = ''
if @cityId >= 999999
set @strLabel = SELECT City FROM CityData where Id = @cityParentId
-- Need help on getting the parent id -1 in loop
else
if @depName <> ''
set@strLabel = SELECT City FROM CityData where Id = @cityParentId
else
set@strLabel = 'General'
RETURN @strLabel
END
GO
---- My Query
SELECT Id, GetFirstLevelLabel(ParentID) as City, Location, Amt
, Amt
FROM CityData
Result Expected
CityLocationAmt
Bangalore BangaloreMain 20
Bangalore North Bangalore 10
Bangalore North Bangalore - Area-1 5
Bangalore North Bangalore - Area-2 15
Bangalore South Bangalore 7
Others Coimbatore 4
Others Coimbatore South 5
Others Tirupur 1
Others Avinashi 3

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Thank you very much for the readily consumable test data. It does make things much more clear and easier to concentrate on code instead of building test data.

Your requested output actually has a small error in it. The South Bangalore (value = 7) has an error because it's actually a child of Bangalore (value = 20). The output for South Bangalore should be 20*7 or 140. With that in mind, the following code will do as you wish. You just need to replace the code you have in your recursive function (which is VERY expensive, BTW) with the following. Of course, I've included the test data, as well.

--CREATE TABLE

CREATE TABLE [dbo].[CityData](

[Id] [int] NULL,

[ParentID] [int] NULL,

[City] [nchar](20) NULL,

[Location] [nchar](50) NULL,

[Amt] [int] NULL

) ON [PRIMARY]

;

---INSERT DATA

insert into CityData

SELECT 1,-1,'Bangalore','BangaloreMain',20 UNION ALL

SELECT 2,1,'NULL','North Bangalore',10 UNION ALL

SELECT 3,2,'NULL','North Bangalore - Area-1',5 UNION ALL

SELECT 4,2,'NULL','North Bangalore - Area-2',15 UNION ALL

SELECT 5,1,'NULL','South Bangalore',7 UNION ALL

SELECT 6,-1,'Others','Coimbatore',4 UNION ALL

SELECT 7,6,'NULL','Coimbatore South',5 UNION ALL

SELECT 8,7,'NULL','Tirupur',1 UNION ALL

SELECT 9,7,'NULL','Avinashi',3

;

--===== Solve the problem

WITH

cteHierarchy AS

(--==== Get the "Root" row

SELECT ID, ParentID, City, Location, Amt, AmtProd = Amt,

SortPath = CAST(CAST(ID AS BINARY(4)) AS VARBINARY(4000))

FROM dbo.CityData

WHERE ParentID = -1

UNION ALL

--==== Recurse through each level of the hierarchy

SELECT t.ID, t.ParentID, h.City, t.Location, t.Amt, AmtProd = h.AmtProd * t.Amt,

SortPath = CAST(h.SortPath + CAST(t.ID AS BINARY(4)) AS VARBINARY(4000))

FROM dbo.CityData t

INNER JOIN cteHierarchy h ON t.ParentID = h.ID

)--==== Display the output from the CTE above

SELECT *

FROM cteHierarchy

ORDER BY SortPath

;

Note that I've left all working columns in the output. You can obviously do away with those in the final SELECT.

For more information on sorting the Hierarchy into the expected sort order, please see the following article.

http://www.sqlservercentral.com/articles/T-SQL/72503/

Here's the output from the code above.

ID ParentID City Location Amt AmtProd SortPath

-- -------- --------- ------------------------ --- ------- --------------------------

1 -1 Bangalore BangaloreMain 20 20 0x00000001

2 1 Bangalore North Bangalore 10 200 0x0000000100000002

3 2 Bangalore North Bangalore - Area-1 5 1000 0x000000010000000200000003

4 2 Bangalore North Bangalore - Area-2 15 3000 0x000000010000000200000004

5 1 Bangalore South Bangalore 7 140 0x0000000100000005

6 -1 Others Coimbatore 4 4 0x00000006

7 6 Others Coimbatore South 5 20 0x0000000600000007

8 7 Others Tirupur 1 20 0x000000060000000700000008

9 7 Others Avinashi 3 60 0x000000060000000700000009

(9 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)