October 14, 2010 at 8:50 am
You could also try the ITVF version:
CREATE Table dnszone (
alias varchar(50),
dnsname varchar(50)
)
INSERT INTO dnszone
SELECT '1.1.1.1', 'dummydummy.uk'
UNION ALL SELECT 'dummydummy.uk', 'dummy.uk'
UNION ALL SELECT '1.1.1.1', 'dummydummier.uk'
UNION ALL SELECT 'dummydummier.uk', 'dummier.uk';
GO
CREATE FUNCTION [dbo].[alias2dnsname] (
@alias NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN (
WITH zones AS (
SELECT dnsname
FROM dnszone
WHERE alias = @alias
UNION ALL
SELECT B.dnsname
FROM zones AS A
INNER JOIN dnszone AS B
ON A.dnsname = B.alias
)
SELECT dnsName =
COALESCE (
STUFF (
(
SELECT ',' + dnsName AS [text()]
FROM zones
FOR XML PATH('')
)
, 1, 1, SPACE(0))
, @alias)
)
GO
SELECT *
FROM dbo.[alias2dnsname]('1.1.1.1')
Scalar functions are generally slower than their table counterpart.
-- Gianluca Sartori
October 14, 2010 at 9:02 am
Oh nice, yes - definitely use that over the scalar.
I've not yet had the "pleasure" of using FOR XML in the wild.
October 14, 2010 at 9:07 am
Ehm... my function doesn't seem to return the correct results.
Let me work on it a bit.
-- Gianluca Sartori
October 14, 2010 at 9:11 am
Pffft - I always said FOR XML was rubbish 😉
October 14, 2010 at 9:26 am
i think i'm going back to cursors.
do you have any idea why my first solution returns a null?
October 14, 2010 at 9:29 am
Why? You have a working solution and someone working on an improved solution.
October 14, 2010 at 10:06 am
Well... go on then 😉 i'm working on a solution as well 🙂
hey wait... i still have to test your last solution 😛
October 14, 2010 at 10:16 am
This seems to do the trick:
ALTER FUNCTION [dbo].[alias2dnsname] (
@alias NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN (
WITH zones AS (
SELECT dnsname, alias
FROM dnszone AS A
WHERE A.alias = @alias
UNION ALL
SELECT B.dnsname, B.alias
FROM zones AS A
INNER JOIN dnszone AS B
ON A.dnsname = B.alias
)
SELECT dnsName =
COALESCE (
STUFF (
(
SELECT ',' + dnsName AS [text()]
FROM zones
WHERE ISNUMERIC(LEFT(alias,1)) = 0
FOR XML PATH('')
)
, 1, 1, SPACE(0))
, @alias)
)
I don't know if the sort order of the tokens matters. If it is so, I suppose it should be the recursion level, that can't be captured, making my code quite useless.
Anyway this is the best I could come up with.
-- Gianluca Sartori
October 18, 2010 at 3:10 am
I think this is not a problem that can be solved with a set based programming language. Gianluca, your solution doesn't work. Returns the correct values for 1.1.1.1 but fails for all the others.
As the table gets populated via java, i'm going to have the parser modified on my needs.
Thanks guys 😉
October 18, 2010 at 3:39 am
OK, looks like I've got it working:
ALTER FUNCTION [dbo].[alias2dnsname] (
@alias NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN (
WITH zones AS (
SELECT dnsname, alias
FROM dnszone AS A
WHERE A.alias = @alias
UNION ALL
SELECT B.dnsname, B.alias
FROM zones AS A
INNER JOIN dnszone AS B
ON A.dnsname = B.alias
)
SELECT dnsName =
COALESCE (
STUFF (
(
SELECT ',' + dnsName AS [text()]
FROM zones
WHERE dnsname NOT IN (
SELECT alias
FROM zones
)
FOR XML PATH('')
)
, 1, 1, SPACE(0))
, @alias)
)
GO
SELECT '1.1.1.1', *
FROM dbo.alias2dnsname('1.1.1.1')
UNION ALL
SELECT '1.1.1.2', *
FROM dbo.alias2dnsname('1.1.1.2')
UNION ALL
SELECT '1.1.1.3', *
FROM dbo.alias2dnsname('1.1.1.3')
There's ALWAYS a set-based solution.
BTW, this is not a pure set-bases solution, because this is based on a recursive CTE, that is hidden RBAR.
Anyway, it seems to work. 🙂
-- Gianluca Sartori
October 18, 2010 at 3:53 am
Brilliant!!!
😉
works like a charm
October 18, 2010 at 3:59 am
It took me a while to get it to work...
Thanks for the feedback, glad it works for you.
-- Gianluca Sartori
October 18, 2010 at 4:22 am
and it's going to take me quite some time to adapt it to real tables and rules (as i mentioned, i proposed here a simplified version) but this is something i want to do on my own.
Gianluca, if you see fit, would you please explain a bit how the select on the CTE works? 😉
October 18, 2010 at 4:30 am
WITH zones AS (
SELECT dnsname, alias
FROM dnszone AS A
WHERE A.alias = @alias
UNION ALL
SELECT B.dnsname, B.alias
FROM zones AS A
INNER JOIN dnszone AS B
ON A.dnsname = B.alias
)
SELECT *
FROM zones
This is a recursive CTE. It selects all the rows that match @alias, then it gets unioned with the recursive query, in this case all rows in dnszone that match the predicate dnszone.alias = zones.dnsname, with zones as the CTE itself.
The recursive query gets repeated until it returns no rows or MAXRECURSION gets reached.
If you get in trouble with the "real" query, you can post another thread or keep posting on this one.
If you don't want to post real production code on a forum, you can drop me a PM.
-- Gianluca Sartori
October 18, 2010 at 4:47 am
Well..i know how recursive CTEs work 😀
i am interested in the select on the rCTE.
Anyway... now it works on the real problem as well 😉
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply