January 18, 2014 at 3:17 pm
I have an existing function and need to alter function to give result of the values multipiled until its parent is reached.
need two seperate functions for city and amt columns
need to also display the parent-description
--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].[GetAmt]
(
@AmtIn int,
@intParId int
)
RETURNS int
AS
BEGIN
DECLARE @ParId INT
DECLARE @Amt INT
SELECT @Amt=Amt,@ParId=ParentID from CityData where Id = @intParId
if @ParId<>-1
BEGIN
Set @Amt = dbo.GetAmt(@Amt*@AmtIn,@ParId)
END
else
begin
Set @Amt = @AmtIn
end
RETURN @Amt
END
GO
--- Note : I have given one function only so that i can implement the same in GetCity also.
---- My Query
SELECT Id, ParentID, , dbo.getCity(City)
, Location, Amt
, dbo.getamt(Amt,Id)
FROM CityData
Result Expected
City Location Amt
Bangalore BangaloreMain 20
Bangalore North Bangalore 200
Bangalore North Bangalore - Area-1 1000
Bangalore North Bangalore - Area-2 3000
Bangalore South Bangalore 140
Others Coimbatore 4
Others Coimbatore South 20
Others Tirupur 20
Others Avinashi 60
In my current query, i am getting Immediate Parent data only
Ie :
3 2 NULL North Bangalore - Area 1 5 250
4 2 NULL North Bangalore - Area 2 15 2250
where in i am expecting the" Amt" child to be muliplied with all the parents value
and the Department to display the highest parent text.
EXPECTED
================
3 2 Bangalore North Bangalore - Area 1 5 1000
4 2 Bangalore North Bangalore - Area 2 15 3000
Formula for Amt : 250* 100*20
currently i am able to get 250* 100 .
January 20, 2014 at 8:09 am
Excellent job posting ddl and sample data.
I don't understand your desired output. Also, you have scalar functions which are horrible for performance. The one you posted the code for is a recursive scalar function. This will very likely perform worse than a cursor. I am certain that we can accomplish your requirements without either of your scalar functions.
Can you explain more clearly what the logic is for the expected output? Also please post getCity function, we can get rid of that one too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2014 at 8:34 am
As Sean said, a recursive function will give a horrible performance.
For cases like this one, I like to use recursive CTEs, but you might to be aware that performace will degrade if there are many levels queried.
Here's an option. Feel free to ask any questions if you don't understand how does it work.
WITH rCTE AS(
SELECT Id, City, Location, Amt
FROM CityData
WHERE ParentID = -1
UNION ALL
SELECT c.Id, r.City, c.Location, c.Amt * r.Amt
FROM CityData c
JOIN rCTE r ON c.ParentID = r.Id
)
SELECT *
FROM rCTE
ORDER BY Id
EDIT: Reviewing the part of the getCity function, I changed the source of the City column in the recursive part of the CTE.
January 20, 2014 at 9:21 am
Hi
The above Cte works fine.
I need to use recursive funtion since i have the stored proedure with more data columns.
In my current funcion, i am unable to do recursive of an nvarchar
Create FUNCTION [dbo].[fillCity]
(
@RowIdIn int
)
RETURNS varchar(128)
AS
BEGIN
DECLARE @City nvarchar(128)
select @City = city , @ParentId = ParentId from CityData where id = @RowIdIn
IF LTRIM(RTRIM(@City)) = ''
set @ret = dbo.fillCity( @ParentId)
else
set @ret = @City
RETURN (@ret)
END
recursive is not working...
set @ret = dbo.fillDepartment ( @ParentId)
January 20, 2014 at 9:30 am
Shanmuga Raj (1/20/2014)
I need to use recursive funtion since i have the stored proedure with more data columns.
No, you don't. You need to understand how the query works to use it on your complete solution. You keep posting obfuscated problems and change the requirements with each reply.
January 20, 2014 at 9:55 am
Sorry,
I was not able to find the soultion with the above cte code and i need to use recusive function for the same.
January 20, 2014 at 10:01 am
Shanmuga Raj (1/20/2014)
Sorry,I was not able to find the soultion with the above cte code and i need to use recusive function for the same.
A recursive scalar is about the worst possible solution for this. It will be slower than molasses on frozen tundra. Just because you can't figure it out does not mean you should resort to recursive scalar functions.
Let's try to figure out what this thing should do. Let's say you are going to pass in 3 as RowIdIn. What do you want for output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2014 at 3:57 pm
Damn! I swear that I just answered this particular problem with code that could be used in an iTVF. I wonder if I forgot to hit "Post Reply" or if this is another person asking the identical question.
--Jeff Moden
January 20, 2014 at 4:01 pm
I found the post I was talking about. http://qa.sqlservercentral.com/Forums/Topic1532465-391-1.aspx#bm1532469
@shanmuga Raj,
Did you even look at the solution I posted? It provides the exact answer you wanted. And stop double posting. You're just dividing answers and wasting peoples' time when you double post.
No more posts on this thread please. The problem was solved on the URL cited above.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply