Clever Geek Handbook

Hierarchical data error management: REPEAT is not a recognized built-in function name. "

I am a beginner in SQL. I am researching hierarchical data management with ms sql r2 2008. Here is the link I referenced http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ But now I have a problem with Finding the depth of nodes I copy sql query to my ms sql

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

He brings me back

Msg 195, Level 15, State 10, Line 1 "REPEAT" is not a recognized built-in function name.

Can anyone help me fix the sql query?
Also, does anyone have a better solution for managing hierarchical data?

+3


source to share


3 answers


You are using the code from the MySQL article on MS SQL Server.

A lot will translate well, but a lot won't. As @FilipDeVos says, the equivalent REPEAT()

in SQL Server is REPLICATE()

and you will find many more cases like this.

When you find them, you need to search the string for SQL Server for the equivalent MySQL statements used.


As far as the different methods of managing hierarchies go, the most common are probably contiguous lists and then the nested sets that you use in this article. It depends on your needs, keep exploring, there is no one size fits all golden answer.



EDIT

If you continue this article and ask each difference here, you will be here forever. You should search the Internet for your answers;)

But for now, after your added question about CONCAT()

, try this ...

SELECT REPLICATE(' ', COUNT(parent.name) - 1) + node.name AS name
+2


source


I think REPEAT is a mysql command and you tagged MSSSQL, so it probably won't work.

For hierarchical data in Sql Server 2008, look at the HierarchyId data type. I have added links to several MSDN articles that should point you in the right direction.



http://msdn.microsoft.com/en-us/library/bb677290.aspx

http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

+1


source


SQL Server has a different syntax than mysql. The query can be rewritten as follows

SELECT REPLICATE(' ', COUNT(parent.name) - 1) + node.name AS name
  FROM nested_category AS node
  JOIN nested_category AS parent
    ON node.lft BETWEEN parent.lft AND parent.rgt
 GROUP BY node.name
 ORDER BY node.lft;
  • A function REPEAT()

    in MySQL can be replaced with a function REPLICATE()

    in SQL Server.
  • The function CONCAT()

    in MySQL has no equivalent in SQL Server, but string concatenation can be done using an operand +

    .
  • Joining to SQL Server can be better written with construct INNER JOIN

    as it is more expressive (although the semicolon approach works fine).
+1


source






More articles:


All Articles