Advanced SQL: Return table + 1 calculated column

Can someone help me figure out how to set up an SQL query in outsystems? I am using an MS SQL database. The query works when I run it directly on the database, but am having problems when I try to put it into outsystems because the column count is different then what is listed in my output entities/structures. I think I am missing something simple. I have the Request table set up as an output entity, but I don't know how to set up my output properly to include the calculated column 'AllRequestsAreComplete'. 

In the SQL tab of the advanced SQL editor, when I click test, I get the following error: 

Database returned the following error: Error in advanced query SQL1: Column count doesn't match output structure attribute count.

WITH MYCTE ([PID],[AllRequestsAreComplete]) AS
(SELECT [PID],
    CASE WHEN SUM(CASE WHEN [ISREQUESTCOMPLETE] <> 'TRUE' THEN 1 ELSE 0 END) = 0
        THEN 'TRUE'
        ELSE 'FALSE'
        END
AS [AllRequestsAreComplete]
FROM {Request} AS R
GROUP BY [PID])

SELECT * , [AllRequestsAreComplete]
FROM {Person} P
RIGHT JOIN MYCTE M ON M.[PID] = P.[PID]





Hi Ryan,

You need define collumns in select statement to correspond to output structure.

Hi Ryan,


I'm not entirely sure what you try to accomplish with that query, but by the looks of it, the resulting columns are all columns from person + 1 extra calculated column indicating whether all this persons requests are complete.  

You say that you have the Request table as output entity, so that doesn't match !


I had a go at it, assuming that you want to get an overview of persons, and for each person, an indication whether all his requests are complete, I made this little data model :


I think it's cleanest to define a dedicated structure holding exactly the columns that you want in the output of the query, and no more then the ones you want (so don't output person.*)



So add this structure as the only output of your query.  I rewrote the query a little to make it easier for me to understand what is going on.


Notice I'm not querying for person.*.  If I do that, and the person attributes change, my query would be broken. (You could try adding a person record as attribute in the structure instead of separate columns for id and name and see if that also works, in that case the query isn't that vulnerable to database changes)

with these people and requests in my database...


I get this result from the query :



This is maybe not exactly the information you were hoping to extract, but it demonstrates the use of a structure for sql output.  For example, I'm not sure you want Ringo in there since he never had any requests to complete.


Hope this helps you along,

Dorine