SQL query- Getting output from two entities

I am trying to populate a table using the SQL query below.   The query itself seems to run fine, I see all of the columns that I expect to see in the test output from within the SQL widget but when I debug the step I am not seeing the {User} entity being included in the SQL output.   The Case Output is there as expected but nothing is provided for User  (so I don't have access to the user names associated with the case activities)


I'm pretty new to Outsystems so I'm sure I'm missing a critical step here.   What is the trick to getting all of the value to be output in the list so the table can display them?   Thanks in advance for any tips.


SyntaxEditor Code Snippet

SELECT {Cases}.*, {User}.*
FROM {Cases} 
INNER JOIN {User} ON [CreatedBy] = {User}.[Id]
WHERE
[CreatedDt] BETWEEN  @FromDate AND @ToDate
AND [CreatedBy] IN (@Users)

Hey Josh, 


If you remove the last line of the query, i think it will work the way you intended. 

Then all you need is an output structure with the fields you wish to populate from the query.

Hugo Ferreira wrote:

Hey Josh, 


If you remove the last line of the query, i think it will work the way you intended. 

Then all you need is an output structure with the fields you wish to populate from the query.



I tried creating a structure with values for Case entity and User entity but when I add that to the SQL widget and test I get invalid cast errors.   I assume when it's trying to append the results to the Case and User entity values?

Should I be creating the structure with different attributes that correlate with all of the entity attributes?   I have tried both ways but neither seem to be working as I would expect.      If I use the structure with the individual attributes that I want to display I get a cast error saying it cannot convert DateTime to Int32.



Solution

Josh Herron wrote:

Hugo Ferreira wrote:

Hey Josh, 


If you remove the last line of the query, i think it will work the way you intended. 

Then all you need is an output structure with the fields you wish to populate from the query.



I tried creating a structure with values for Case entity and User entity but when I add that to the SQL widget and test I get invalid cast errors.   I assume when it's trying to append the results to the Case and User entity values?

Should I be creating the structure with different attributes that correlate with all of the entity attributes?   I have tried both ways but neither seem to be working as I would expect.      If I use the structure with the individual attributes that I want to display I get a cast error saying it cannot convert DateTime to Int32.



The problem is in your structure. Define individual fields inside the structure that match the output of the query.


Solution

Hi all,
First question that you have to ask yourself is: Do i need all the fields from Cases and Users?
If not, create a structure with only the fields that you need and select the specific fields of the sctucture.

By from what i can see, you have a wrong data type on the column Case from the structure Cases

Thanks to you both.  I updated the query to only pull the 6 columns that I really need to display now it seems to be mapping to the structure as expected.


(I deleted the other test structure that contained the Case and User entities rather than the attributes)

One other question, which is a bit of a tangent... but normally when I use an aggregate to populate my tables, the aggregate contains a record count that is used in the list navigation widget.  (to paginate through the table records)


When I get the SQL output I have my structure values and I append those to a local variable of type list>Structure and the records are displayed properly in the table... but I cannot figure out how to get table pagination to work with that table.