none
T sql dose not work in SQlCompact

    Question

  • im useing a T Sql in SQlCompact database but it dose not work the function is stuff() that for gathering values in on cell this is the select Statement but it shows me an error asking for help

    SELECT Pepole.ID, Pepole.FNA, Pepole.LNA, STUFF ((SELECT ', ' + Name AS Expr1 FROM Special AS S2 WHERE (ID = PeopleTarget_1.SpecialID) FOR XML PATH('')),,,) AS Names FROM Pepole INNER JOIN PeopleTarget AS PeopleTarget_1 ON Pepole.ID = PeopleTarget_1.PeopleID INNER JOIN Special AS Special_1 ON PeopleTarget_1.PeopleID = Special_1.ID


    Saturday, April 05, 2014 11:12 AM

Answers

All replies

  • Try the below:(Not tested), if you have any more issues, please provide DDL and DML.

    SELECT Pepole.ID, Pepole.FNA, Pepole.LNA, 
    STUFF ((SELECT ', ' + Name AS Expr1 FROM Special AS S2 WHERE (ID = PeopleTarget_1.SpecialID) FOR XML PATH('')),1,1,'')
     AS Names FROM Pepole INNER JOIN PeopleTarget AS PeopleTarget_1 ON Pepole.ID = PeopleTarget_1.PeopleID 
    INNER JOIN Special AS Special_1 ON PeopleTarget_1.PeopleID = Special_1.ID

    Saturday, April 05, 2014 12:29 PM
  • it shows an error !!!!!!!!!!
    Saturday, April 05, 2014 12:38 PM
  • Whats the error message, please provide DDL and DML for your scenario to help you better.
    Saturday, April 05, 2014 12:40 PM
  • SELECT        Pepole.ID, Pepole.FNA, Pepole.LNA, STUFF
                                 ((SELECT        ', ' + Name AS Expr1
                                     FROM            Special AS S2
                                     WHERE        (ID = PeopleTarget_1.SpecialID) FOR XML PATH('')),,,) AS Names
    FROM            Pepole INNER JOIN
                             PeopleTarget AS PeopleTarget_1 ON Pepole.ID = PeopleTarget_1.PeopleID INNER JOIN
                             Special AS Special_1 ON PeopleTarget_1.PeopleID = Special_1.ID 
    Saturday, April 05, 2014 12:48 PM
  • the error message "there was an error parsing the query"

    Saturday, April 05, 2014 12:49 PM
  • Try the below:

    Create Table Pepole(Id int,FNA int,LNA int)
    Insert into Pepole Values(1,100,200)
    
    Create Table PeopleTarget(PeopleId int,SpecialID int)
    Insert into PeopleTarget Values(1,1),(1,1)
    
    Create Table Special(ID int,Name varchar(50))
    Insert into Special Values (1,'SQL'),(1,'Server')
    
    
    SELECT distinct Pepole.ID, Pepole.FNA, Pepole.LNA, 
    STUFF ((SELECT ', ' + Name FROM Special AS S2 WHERE (ID = PeopleTarget_1.SpecialID) FOR XML PATH('')),1,1,'')
     AS Names 
     FROM Pepole INNER JOIN PeopleTarget AS PeopleTarget_1 ON Pepole.ID = PeopleTarget_1.PeopleID 
    INNER JOIN Special AS Special_1 ON PeopleTarget_1.PeopleID = Special_1.ID
    
    
    Drop table Pepole,PeopleTarget,Special

    Saturday, April 05, 2014 12:51 PM
  • ooooooooooooooh it still shows me a wrong :'((


    • Edited by Aboudeh Saturday, April 05, 2014 1:06 PM
    Saturday, April 05, 2014 12:57 PM
  • Where is your error message? Please provide details to help you.
    Saturday, April 05, 2014 1:14 PM
  • I don't believe SQL Server CE supports XML or subqueries so you'll need to use a different technique.  I suggest you post this question to a Compact forum http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlce


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, April 05, 2014 1:17 PM
  • Hello,

    SQL CE = Compact Edition supports only a small subset of Transact-SQL commands; see SQL Reference (SQL Server Compact) . XQuery / XML is not supported.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Sunday, April 06, 2014 7:59 AM