Model and Query Data (cont.)
Write SQL Queries
This lesson is part of the Developer Online Class for OutSystems 9 course.

in this session we will see how you can write your own
SQL queries for this matter we will
add a top clients list to our dashboard so
what we want to do is here in a dashboard
after the top products create a
top clients list and again to do this we will need to
go into the preparation and fetch this information from the database
but instead of using an aggregate we're going to use a new tool we're
going to use this
SQL tool the SQL tool allows you to write your own database queries
you need to know the SQL language to use this tool
and your statement must be compatible with the database you are using
let's call this the top clients
and here in this SQL tool
you write down your
SQL statement and in this cases
I'm going to copy paste it from the clipboard
you have you have this statement in the resources of this
lesson be sure to download it and
in this statement the difference from the standard SQL
is that in this statement you reference
the entities and the attributes using this syntax
entities are prefixed and suffixed with a curly brackets
and attributes use square brackets
the platform we'll transformed this notation
to use the database table
for the entity and the attributes or the column
name for the attribute in the entity
now we have here we have here an error
in our statement and basically this is because we need
we need to define this input parameter
for our query and we're going to do this
here in the parameters add a query parameter
and we're going to call it CanceledSatusId
we want to set this data type to be
a Status Identifier this query parameter
allows your SQL statement to be dynamic based on the value
of this parameter so here we will need to fill in this value
so that in runtime this statement can be dynamic
this means that our statement is correct now so
once you compose your SQL statement you have to test it
to see its output so let's go ahead and test
our statement and it seems that
we have to define an output structure for
this SQL statement and and we'll have to do this
by creating here in new object in the Data tab
which will be
a structure a structure is a datatype composed of attributes
compared to entities structures don't have
an identifier and also can't be stored
in the database so so so let's create our
structure for our query this will be the top clients
and we have to define here
the attributes which will be the Name of the client
the NumberOfPurchases
and the amount
this client has spent now we can
drag-and-drop the structure here to the output structure of the
SQL query and now we can finally test it
okay so this
gives me results you'll see the results here in the test output
you will notice that we also have a test inputs
so this means that we can define the values
for these parameters here and actually I'm going to define the value for
but canceled status in this case I want this to be
this value here remember that we changed
the data type to a status identifier which is a static entity
so we can reference directly the status and
if now I test these values
you see that Eric here which had two orders one of those
was canceled and now the data is correct so that we can put
on on our dashboard okay so we're done here
and we need to set
the parameter value here as well so this will be
status canceled
there and now we can go back to the screen
add a table records here to the screen
and bind it to
the SQL query that we have on the preparation
this one and
we can now drag and drop this structure
here into into the table records
and final thing to do is to again set
the number of clients to show to be
5 and we're almost done I just want to do one more thing which is
actually to create a menu for my application so that I am able to
go into the dashboard in also go into the orders
screen so drag and drop the dashboard
here to create
the Dashboard menu and also the order screen
to create the Orders menu and were done
let's publish and see our application
and here our dashboard now with a top products
and a top clients list and it seems that Allan
here is our best customer and that's it