【Query Builder】Sorting the result of UNION query

# Introduction #
UNION query can be designed with Sharperlight Query Builder. It is useful for Combining data from different sources.

The final dataset is created as a stack of the results from each union query. While the results of each query can be sorted by specifying criteria, the entire final dataset cannot be sorted as a whole.  

In this post, Let's see how to sort the final result of a UNION query in Sharperlight.

# UNION Query #
Creating a UNION query.
Open **Query Builder** from Sharperlight application menu.
### The 1st Query (Parent Query) ##
Instead of actually retrieving the data from the database, I create a UNION query with the comma-separated datasets.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/07560653-bc69-a737-d8a2-93132370c987.png)
Choose **System** as `Product`.
Use **Custom Define Dataset** as `Table`.
Then create a dataset for the main query by clicking the lookup button.
This opens a text editor. Write the test data on it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ba202161-902f-8499-c6ca-aec34ddabaf3.png)
```
"Invoice Number::String","Amount::Number", "Data from::String"
"A100",1234.55,"Main Query"
"A200",1618.00,"Main Query"
"B300",3141.00,"Main Query"
```
Design the outputs like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/571e613e-8726-bd2c-ac2f-400d5abcd34d.png)

### The 2nd Query ###
Right-click on Outputs pan, and select `Query Unions` to define the 2nd query. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8fd3f307-1c48-ef27-712d-f3bc8fa96748.png)
This opens the Query Unions manager. Click the `New` icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cf597797-1d92-5239-03f6-4d194e9ce4cf.png)
It creates the 2nd query entry. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4b67a31f-23a3-7745-4c9d-990813559369.png)
Double-click on it or click `Edit Query` button.
This opens Query Builder, and the basic definition is inherited from the parent.
Just the custom dataset needs to be editted.
It's like this.
```
"Invoice Number::String","Amount::Number","Source::String"
"B200",1234.55,"The 2nd Query"
"B100",1618.00,"The 2nd Query"
"C100",3141.00,"The 2nd Query"
```
`OK` to save it, and `Unions` button is shown on the parent query builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/32a55fee-af0f-2936-8a3c-2a97cd67ee01.png)

Preview the query and the result dataset is like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b6b42c97-8e4e-5d68-d56e-84181764e8eb.png)

# Sorting #
Let's configure for sorting the final result on Invoice Number.
Right-click on Outputs pan with the parent query, and select `Cascade Query` to define the 2nd query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6b067fd1-29d0-6f93-7a88-b3369f8af3ce.png)
This opens Query Builder for the cascade query.
It's data source is the final result of the UNION query.
Select those three columns to `Outputs`.
Add **Sort** option to `Invoice Number` column.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0f572acf-33dd-97a5-cc41-8aa741c82f05.png)
Preview it and the dataset returned is sorted.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/70111678-6781-ec4e-6b8b-01e8c2ce051c.png)
Click `OK` to save the cascade query.
`Cascade Query` button is shown on the parent query builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e5bae9e8-b6af-b49f-8993-614ac3d823d0.png)

Now we can use this parent query with Sharperlight Publisher as a Web Report or with Sharperlight Excel Addin to extract the data onto a worksheet. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7eac7fb1-b8ca-8c4a-aeff-bf589c3201ac.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4ee24ddb-5a5f-a6ac-5acc-ed599d806224.png)


# Afterword #
This is a good option because it makes sorting UNION queries easier than before.