Hi,

I've attached my issues, on advanced SQL.

Can you help on it plz.

My data in my DB like this,

Username           Name

    ABC                 Thani

    ABC                 Jai

    ABC                 Saravana

    XYZ                  Guru

    XYZ                  Ramesh

    XYZ                  karthi

But i need the result like this,

Username           Name

    ABC                 Thani,Jai,Saravana

    XYZ                  Guru,Ramesh,karthi

Thats why i tried like this,

But it gives the error like below,

Can you tell me, what i did in wrong?...

As per the error message, you need to include the {User}.[Id] in your GROUP BY clause.


Simply add it as below:

GROUP BY u.[Id],
         u.[Username]


The "{User} u" alias is unnecessary and I would actually remove it as a best practice.

Hi Gonçalo Soares,

Thanks for your responds.

If i use u.[Id] in Group By, the names will not be concatenate..

Id       Username           Name

1          ABC                 Thani

2          ABC                 Jai

3          ABC                 Saravana

4          XYZ                  Guru

5          XYZ                  Ramesh

6          XYZ                  karthi

Ids are like this, so how can i use "ID" in Group by, if i use "ID" then the result is the same.

Bcz ID is not redundant. What should i do?? 

Try to take aliases, and when you do group by, even use {User}.[Username]

Cheers,

Nuno Verdasca

Wow, what a strange situation you have: duplicate Usernames in your database!


Aside from that, the problem is your join condition inside the STUFF function.


What you want to do is something like ...

WHERE u.[Username] = u1.[Username]

... instead of a join by {User}.[Id], and remove the GROUP BY clause I told you to add earlier.

Nuno Miguel Verdasca wrote:

Try to take aliases, and when you do group by, even use {User}.[Username]

Cheers,

Nuno Verdasca

Hi Nuno Miguel Verdasca,

Thanks for your responds.

I tried this as well, but even the result is the same , it gives the same error.


Gonçalo Soares wrote:

Wow, what a strange situation you have: duplicate Usernames in your database!


Aside from that, the problem is your join condition inside the STUFF function.


What you want to do is something like ...

WHERE u.[Username] = u1.[Username]

... instead of a join by {User}.[Id], and remove the GROUP BY clause I told you to add earlier.

Hi Gonçalo Soares,

Great thats working, But now the situation is, wants to concatenate Names so its working, But when i'm using "Id" instead of "Name", It gives some error like datatype issue, i pasted it below, can you explain it as well plz.

This is Working now,

But now i need to know this situation is below,

Can you tell me, what i have to do Gonçalo?


Solution

Hi Thanigai,

SQL won't let you concatenate numbers and strings like that.


You'll have to CAST the User Id to a string, as per Nuno's WiseOwl link:

SELECT ', ' + CAST(u1.[Id] AS VARCHAR(200))


Also, make sure your output structure has at least two Attributes of type Text.

Solution

I got it.

Thank you so much Gonçalo Soares and Nuno.

Gonçalo Soares wrote:

Hi Thanigai,

SQL won't let you concatenate numbers and strings like that.


You'll have to CAST the User Id to a string, as per Nuno's WiseOwl link:

SELECT ', ' + CAST(u1.[Id] AS VARCHAR(200))


Also, make sure your output structure has at least two Attributes of type Text.

Hi Gonçalo Soares,

yesterday i checked with my personal environment which is 11.6 version that's working, but i used that code in my UAT environment which is 11.0.511.0, it doesn't take "XML" as key word and gives this error.

take a look on "XML" key word. any idea???

Hmm... tricky.


It would seem that your personal environment is running SQL Server, and your UAT environment is running on Oracle (as per the ORA-00907 error).

As far as I know the STUFF + XML method is only valid for SQL Server databases.


This StackOverflow shows an example on how to perform String concatenation for Oracle databases, and conveniently links to a larger list of examples.

If you need to support two different database engines (for some reason), you would need to maintain two queries, and dynamically select the relevant query based on the result of the GetDatabaseProvider() built-in function.


Alternatively, you can query the entire list of users (preferably using an Aggregate rather than an Advanced Query), and try to perform the match on the application server, rather than the database. Performance-wise, this might not be ideal but if you need to support two different databases I think it would be preferable to keeping two queries.

If you do try this path, I suggest looking at the TextDictionary or HashTable built-in extensions, to avoid filtering/ iterating over the list multiple times.