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.
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.
"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.
The 2nd Query
Right-click on Outputs pan, and select Query Unions to define the 2nd query.
This opens the Query Unions manager. Click the New icon.
It creates the 2nd query entry.
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.
Preview the query and the result dataset is like this.
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.
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.
Preview it and the dataset returned is sorted.
Click OK to save the cascade query.
Cascade Query button is shown on the parent query builder.
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.
Afterword
This is a good option because it makes sorting UNION queries easier than before.