Hi All,
I'm trying to return comma separated values from an entity having multiple records. I'm using STUFF() for this.
Entity on which i'm using stuff() -
Query -
Output - unfortunately I'm getting duplicated rows (comma separated Access is correct, but records are duplicated, I don't know why)
Currently this is working fine (apart from duplicated rows in above image). But earlier, there was a string something as "Facturation Hors Négoce". When this record existed in the entity, I used stuff() again but got error - "string or binary data would be truncated. the statement has been terminated".
error log:
Not sure why this happened, but I couldn't solve this problem. And after 2 hours of scratching my head, I thought lets remove a row. I fortunately removed that "Facturation Hors Négoce" row and this worked. Since then I tried twice but it still doesn't work when I use STUFF() on a column which has a string of texts.
Any help is appreciated. I tried to solve by referring this but couldn't make it work.
PS: if you know how the duplicated rows can be removed, it would be help me a lot.
Thanks & Regards,
Aman Devrath
hello,
that error means you can't allocate the comma separated text, increase your length in DB
Regarding duplicated entries, do a group by.. Distinct maybe solves it too but is not a recommended :P
Hi,
Thank you for the help. I updated the length of the attribute and it worked.
for the duplicating entries, i have to create another entity, update it with duplicated records, and then use another SQL to distinct/group by the records. maybe i'm missing something, i'll have to check.
Hi Aman,
The duplicate records have likely nothing to do with the STUFF(). Have you tried to see what the output is if you remove the STUFF()? Do you still get duplicates?
As for the error, that is not a query error, but a database update error. It happens when you try to save something in the database that's longer than the allocated length. The error stack you show is that of the reactive module, and only shows the error passed by the request. If you have another error that shows the full server-side stack, you'll likely also get the violating attribute.
1. Without STUFF(), isn't it basically a select * statement? (attaching image below)
2. Thank you, I updated the length of the attribute. Just for the sake understanding, how did you identify that it was a database error? was it from the error logs I shared?
I couldn't understand this statement. apologies i'm still learning. "The error stack you show is that of the reactive module, and only shows the error passed by the request. If you have another error that shows the full server-side stack, you'll likely also get the violating attribute."
Thank you,
Yes, without the STUFF it's a simple SELECT, but since STUFF shouldn't create duplicate rows, the problem must be in that SELECT. I suspect that {userWithAccess} doesn't have unique [UserName]/[UserSGID] combinations, but there's additional data.
The reason I know it's a database problem is 1) because it says "SqlException", and 2) because "String or binary data would be truncated" is a very common error, I've seen it dozens of times in our own software (there's always someone who things they can add data that's twice as large as normal :)).
As for the error stack, a "normal" error stack would look something like this:
So you can see where the error originates (here: SQL "qGetPOSPvList" in the Preperation of the POSPVCheckList screen).
Oh, now I understand the error. thank you for the explanation and the image.
About the duplicates - The way you I can explain is : Aman Devrath has 4 access, it is duplicated 4 times. Anirudh Tandel has 5 access, it is duplicated 5 times. It duplicates itself to the number of [Access] that is being assigned to a particular [UserName]. If I add another record with Anirudh Tandel, then there will be then 6 rows.
About {userWithAccess} - I haven't created an index for this entity, but I can create one and add all the attributes to it making a full combination as Unique. So while inserting it can only insert one row and if its duplicate, it will move to another. provided that I handle the exception.
But still the problem stays with SELECT, that I don't understand.
Aman
Do you have another table that contains the unique users and the unique UserSGID? Because if the data looks like something like this:
UserName UserSGID userAccess Aman Devrath 12345 X Aman Devrath 12345 Y Anirudh Tandel 33445 X Anirudh Tandel 33445 Z
And you do SELECT * FROM {userWithAccess} you'll get all those 4 records. It doesn't matter what you put after the SELECT in terms of the number of records you get. If you do SELECT 1 FROM {userWithAccess} you'll get 4 times "1". So in your select, selecting [UserName] and [UserSGID] will give you those 4 records as well. But because you omit [userAccess], you'll get (seemingly) duplicate rows. One solution would be, as Fábio suggested, to use SELECT DISTINCT, or a GROUP BY, but that still wouldn't solve the root problem.
Note all this is basic querying, whether you use an aggregate or SQL. It has nothing to do with OutSystems per se.
Also, it seems that [UserName] should not appear in "userwithAccess" at all - it seems very redundant, if the UserSGID already uniquely identifies the user. I'd expect there to be some user entity that holds both the UserName and UserSGID, and you'd join that to the userWithAccess entity on the UserSGID (or, use an Identifier instead of the SGID), and then you won't have the problems with the duplicate records.