Database

Mycroft Holmes15-Apr-09 1:21

Mycroft Holmes

15-Apr-09 1:21

I had one of my devs ask the exact same question in SQL Server, he got the same answer - not with SQL syntax.

Never underestimate the power of human stupidity
RAH

Muammar©15-Apr-09 10:30

Muammar©

15-Apr-09 10:30

Ok, Thanks mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

i.j.russell15-Apr-09 9:06

i.j.russell

15-Apr-09 9:06

I don't think that any of the leading sql systems will allow you to express your query in the way that you want. However, whilst you can't write it like that, the query processor will actually do exactly what you want anyway; It will not re-evaluate the aggregates if they are used in further calculations in the same logical query phase, it will re-use what it already has.

Rewrite recursive proc so we can do this in UDF instead? (and more efficient)

Muammar©15-Apr-09 10:30

Muammar©

15-Apr-09 10:30

Thanks mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

devvvy14-Apr-09 18:02

Re: Rewrite recursive proc so we can do this in UDF instead? (and more efficient)

14-Apr-09 18:02

hello - I bang my head against the wall trying to rewrite this recursive TSQL proc so we can do this in UDF instead? (and more efficient and to be able to bind it to view column... etc)

<br />
CREATE PROCEDURE spRecursiveGetChildren <br />
(<br />
    @ObjectId bigint, <br />
    @ObjectClassifier varchar(255),<br />
    @TempId varchar(36)<br />
)<br />
AS<br />
BEGIN<br />
	DECLARE @CursorName varchar(100)<br />
	DECLARE @SQL varchar(4000)<br />
	DECLARE @NSQL nvarchar(4000)<br />
	<br />
	-- Dynamic sql + cursor name because there'll be a recursive call and we'd run into CURSOR name conflict<br />
	SET @CursorName = ''spGetChildren_cursor_'' + CAST(@ObjectId as varchar(20)) + ''_'' + REPLACE(CAST(@ObjectClassifier AS varchar(70)), ''.'', ''_'')<br />
	<br />
	PRINT @CursorName<br />
						<br />
	INSERT INTO [dbo].[TMP_HierarchyMap] <br />
		(<br />
		ParentId,<br />
		ChildId,<br />
		ParentType,<br />
		ChildType,<br />
		...<br />
		TmpUID,<br />
		ReferencedId<br />
		)<br />
		SELECT <br />
            ParentId,<br />
            ChildId,<br />
            ParentType,<br />
            ChildType,<br />
            ...<br />
            @TempId,<br />
            Id<br />
            FROM HIERARCHYMAP M1<br />
            WHERE<br />
            ParentId = @ObjectId<br />
            AND<br />
            ParentType = @ObjectClassifier<br />
			AND<br />
			NOT EXISTS(SELECT 1 FROM TMP_HIERARCHYMAP M2 WHERE M1.ChildId=M2.ChildId AND M1.ChildType=M2.ChildType AND ParentId=@ObjectId AND ParentType=@ObjectClassifier AND TmpUID=@TempId)<br />
	<br />
	SET @SQL = <br />
		   ''DECLARE @ThisChildId bigint<br />
			DECLARE @ThisChildType varchar(255)<br />
			DECLARE @CountChildren int<br />
			<br />
		    DECLARE $CURSOR_NAME$ CURSOR FOR <br />
			SELECT ChildId,ChildType <br />
			FROM HIERARCHYMAP <br />
			WHERE  <br />
				ParentId = $OBJECTID$<br />
				AND ParentType = ''''$OBJECTCLASSIFIER$''''<br />
				<br />
				OPEN $CURSOR_NAME$<br />
				FETCH $CURSOR_NAME$ INTO @ThisChildId, @ThisChildType<br />
					<br />
				WHILE @@FETCH_STATUS = 0<br />
				BEGIN		<br />
					<br />
					SELECT @CountChildren = count(1) FROM HIERARCHYMAP WHERE ParentId = @ThisChildId AND ChildType = @ThisChildType<br />
					IF (@CountChildren>0)<br />
						BEGIN<br />
						<br />
						-- ** Recursive call<br />
						EXEC spRecursiveGetChildren @ThisChildId, @ThisChildType, ''''$TEMPID$''''<br />
						END<br />
					FETCH $CURSOR_NAME$ INTO @ThisChildId, @ThisChildType<br />
   				END<br />
					<br />
				CLOSE $CURSOR_NAME$<br />
				DEALLOCATE $CURSOR_NAME$<br />
				''<br />
		SET @SQL = REPLACE(@SQL, ''$CURSOR_NAME$'', @CursorName)<br />
		SET @SQL = REPLACE(@SQL, ''$OBJECTID$'', CAST(@ObjectId as varchar(20)) )<br />
		SET @SQL = REPLACE(@SQL, ''$OBJECTCLASSIFIER$'', CAST(@ObjectClassifier as varchar(70)) )<br />
		SET @SQL = REPLACE(@SQL, ''$TEMPID$'', CAST(@TempId as varchar(36)) )<br />
		<br />
		SET @NSQL = CAST(@SQL AS nvarchar(4000))<br />
		<br />
		PRINT @NSQL<br />
		<br />
		-- Actually executing recursive call! This prevents me from repackage this as UDF instead.<br />
		EXEC sp_executesql @NSQL<br />
END<br />

dev

Ashfield14-Apr-09 21:00

Ashfield

14-Apr-09 21:00

Assuming you are using SQL2005/2008 take a look at CTE (Common Table Expression). These make recursion really easy.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Got recursive CTE sql but... two more questions.

devvvy15-Apr-09 0:07

Re: Got recursive CTE sql but... two more questions.

15-Apr-09 0:07

thanks I got my recursive CTE expression working but two more questions!

is it good with UDF (user defined function)? and performance better than CURSOR?

NOTE: You can't do these from UDF:
* execute/sp_executesql to run another stored proc
* use table variable as input/output function parameter
* access temp table

Anyway I got it working (but with problem and corresponding cheats to get around them)

<br />
CREATE TABLE [dbo].[HierarchyMap] (<br />
	[Id] [bigint] IDENTITY (1, 1) NOT NULL,<br />
	[ParentId] [bigint] NOT NULL, <br />
	[ChildId] [bigint] NOT NULL,<br />
	[ParentType] [varchar] (255) NOT NULL,<br />
	[ChildType] [varchar] (255) NOT NULL, <br />
	CONSTRAINT [PK_HierarchyMap] PRIMARY KEY CLUSTERED <br />
	(<br />
		[Id] ASC<br />
	)<br />
)<br />

Assuming I only have two rows in the table - select * from HierarchyMap:

<br />
Id      ParentId ChildId  ParentType    ChildType<br />
-----------------------------------------------------------<br />
1	1	 1	  Group	        SystemUser<br />
2	1	 2	  Group	        SystemUser<br />

Now, my CTE sql is as follows and the problems are:
1. Max depth 100 for recursion exceeded
2. Duplicate rows (no idea..)

<br />
WITH Children AS<br />
(<br />
--initialization<br />
SELECT<br />
	[Id],<br />
	[ParentId],<br />
	[ChildId],<br />
	[ParentType],<br />
	[ChildType]<br />
FROM HierarchyMap<br />
WHERE <br />
	ParentId=1 AND ParentType='Group'<br />
UNION ALL<br />
--recursive execution<br />
SELECT <br />
	[MAP].[Id],<br />
	[MAP].[ParentId],<br />
	[MAP].[ChildId],<br />
	[MAP].[ParentType],<br />
	[MAP].[ChildType],<br />
	CH.Depth+1 'Depth'<br />
FROM HierarchyMap MAP INNER JOIN Children CH<br />
ON MAP.ParentId = CH.ChildId<br />
WHERE <br />
Depth<100          -- PROBLEM 1: If I don't limit depth I get error <b>"The maximum recursion 100 has been exhausted before statement completion."</b><br />
)<br />
SELECT ParentId, ParentType, ChildId, ChildType FROM Children <b>GROUP BY Id, ParentId, ParentType, ChildId, ChildType</b> -- PROBLEM 2: I don't ... understand the duplicate rows... which is why I need to do a GROUP-BY... think I did something wrong?<br />

Suggestion?

http://www.mssqltips.com/tip.asp?tip=1520
http://www.setfocus.com/TechnicalArticles/sql-server-2005-tsql-3.aspx
http://stackoverflow.com/questions/634971/sql-server-how-to-limit-cte-recursion-to-rows-just-recursivly-added[^]

dev

Giorgi Dalakishvili15-Apr-09 1:13

Giorgi Dalakishvili

15-Apr-09 1:13

Here is a good article about CTEs: Common Table Expressions[^]

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

Re: Got recursive CTE sql but... two more questions.

i.j.russell15-Apr-09 9:10

i.j.russell

15-Apr-09 9:10

You can set the maxrecursion option to something other than the default of 100. If you set the value as 0 it will recurse as many times as it wants. Just make sure that the query will not lead to an infinite loop.

Re: Got recursive CTE sql but... two more questions.

devvvy15-Apr-09 13:25

TABLE VALUE FUNCTION for MySQL?

15-Apr-09 13:25

THanks guys

dev

devvvy14-Apr-09 16:20

14-Apr-09 16:20

hello

How do I create a table value function? i.e. return a TABLE instead of say an integer or varchar...

Thanks!

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html[^]

dev

File logging.

Ivan200914-Apr-09 8:50

Ivan2009

14-Apr-09 8:50

Can somebody shed light on what is file logging? Does it stand alone or comes as a part of some javascript?
Is it just a set of some classes used to log errors and events? Waiting for some direction..
Thanks

Re: File logging.

Ashfield14-Apr-09 21:03

Ashfield

14-Apr-09 21:03

And what does it have to do with a database forum?

File logging is just a generic term for logging information to a file, as opposed to say a database. There are loads of examples & free code out there to do it, or just roll your own, its simple enough - just think ahead if you are multi-threading.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: File logging.

Ivan200915-Apr-09 6:09

Ivan2009

15-Apr-09 6:09

Thank you for your reply.

Query

CodingYoshi14-Apr-09 6:23

CodingYoshi

14-Apr-09 6:23

How can I write a query which updates the top 200 rows of a table and then selects those rows? Here is what I have so far:

UPDATE TableX
SET Column2= 'XXXXX'
FROM
(
SELECT TOP 200 Column1, Column2, Column3, Column4
FROM TableX
WHERE Column2 IS NULL
)AS RetrievedRows

WHERE TableX.Column2= RetrievedRows.Column2

In the subquery I grab the top 200 rows, in the parent query I update those rows. But how can I select those rows that were just updated?

CodingYoshi

Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07

Re: Query

Eddy Vluggen14-Apr-09 10:10

Eddy Vluggen

14-Apr-09 10:10

You could consider adding a 'last updated' timestamp, if you don't mind the bit extra overhead. That would simplify selecting the last updated records a tad Smile | :)

I are troll Smile | :)

datacolumn boxing

Maverickcool13-Apr-09 22:02

Maverickcool

13-Apr-09 22:02

hi I have a general question

if I write

row[0][0] = 5;

does a boxing occurs ?

should I write (from perfomance look )

row[0][0] = (object)5;

Mycroft Holmes14-Apr-09 17:05

Mycroft Holmes

14-Apr-09 17:05

What database are you using
what is boxing - not in any SQL I know of

Never underestimate the power of human stupidity
RAH

Maverickcool14-Apr-09 23:55

Maverickcool

14-Apr-09 23:55

i'm using SQL 2005
but the boxing is not done by the SQL server
it's done by the CLR

Mycroft Holmes15-Apr-09 1:18

Mycroft Holmes

15-Apr-09 1:18

Sorry, can't help you there I don't use the cls, I know of no one who does. Stupid idea really, another layer of abstraction so the poor dev does not need to learn TSQL.

However I use convert.to????? in C# rather than boxing.

Never underestimate the power of human stupidity
RAH

multiline dynamic tsql - recognize line break?

jai_10124-Apr-09 21:49

jai_101

24-Apr-09 21:49

In C# there are two types of Convertion is used.

<b>Boxing :convert ValueTypes to Reference Types
UnBoxing :Reference Types to convert ValueTypes </b>

From Your code

row[0][0] = 5; ---> This is implicit Boxing
row[0][0] = (object)5; ---> This is Explicit Boxing

You can use any of these to the Conversion.

This is General DataBase MessageBoard. So don't ask C# Questions here use C# MessageBoard.

Bye,
Jai

Rose | [Rose]

devvvy13-Apr-09 20:22

Re: multiline dynamic tsql - recognize line break?

13-Apr-09 20:22

hello

How can you get a complex multiline dynamic T-SQL to execute, I tried ";" don't work, SQL Anaylzer doesn't recognize line breaks.

SET @SQL =
       'DECLARE @ThisParentId bigint;
        DECLARE @ThisParentType varchar(255);
        DECLARE @CountParent int;

        DECLARE $CURSOR_NAME$ CURSOR FOR
        SELECT ParentId,ParentType
        FROM HIERARCHYMAP
        WHERE
            ChildId = $OBJECTID$
            AND ChildType = ''$OBJECTCLASSIFIER$'';

            OPEN $CURSOR_NAME$;
            FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;

            WHILE @@FETCH_STATUS = 0;
            BEGIN

                SELECT @CountParent = count(1) FROM HIERARCHYMAP WHERE ChildId = @ThisParentId AND ChildType = @ThisParentType;
                IF (@CountParent>0)
                    BEGIN
                    EXEC spRecursiveGetParents @ThisParentId, @ThisParentType, ''$TEMPID$'';
                    END;
                FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;
            END;

            CLOSE $CURSOR_NAME$;
            DEALLOCATE $CURSOR_NAME$;
            '
    SET @SQL = REPLACE(@SQL, '$CURSOR_NAME$', @CursorName)
    SET @SQL = REPLACE(@SQL, '$OBJECTID$', CAST(@ObjectId as varchar(20)) )
    SET @SQL = REPLACE(@SQL, '$OBJECTCLASSIFIER$', CAST(@ObjectClassifier as varchar(70)) )
    SET @SQL = REPLACE(@SQL, '$TEMPID$', CAST(@TempId as varchar(36)) )

    PRINT @SQL

    EXECUTE @SQL

Thanks

dev

devvvy13-Apr-09 20:46