【Custom Tables】Defining a custom table

Sharperlight Custom Table allows you to create virtual tables using SQL statements. This can be used when the database you connect to through the data model has tables that are specific to that environment, or when you want to create a view for a data set that you use frequently on a daily basis, but you are not allowed to modify the database.
Here's a quick introduction to creating it.

Launch **Custom Tables** from the [Sharperlight](https://sharperlight.com/) application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/53636f3f-8b0c-cb8c-d476-42b9b75e7037.png)
When the **Custom Tables** dialog opens, use the `New` button or select `New` from  right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/06dd9a74-5f80-7683-6f8f-80467ef90edd.png)
A **product** dialog will appear, allowing you to specify the product to use. This means that by selecting a product (data model), you create custom tables related to the database to which that data model is connected. This time, we will use a product that is connected to the practice database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bff9c141-b3a6-66e1-58b1-4ce9d6c10da3.png)
A message asking if you want to create it with SQL statement will be displayed, so press the `Yes` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/28367fec-8205-b0c8-56b2-19448f8e6e51.png)
Select **SQL Statement** for `Type` and paste the prepared SQL statement into the central text field.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b890742c-ca48-8e6e-db19-3fe4a4bf65cd.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8fbe1759-1e3b-3997-ab05-dca33979202a.png)
Proceed with the `OK` button.
The custom table definition dialog opens.
In the `General` tab fill in **Code** and **Title**. **Code** is the key that represents this custom table. It cannot be changed after this custom table has been used in other Sharperlight objects. If you change it by any chance, reports etc. will not be displayed normally.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fdbd4465-a69c-6659-0b70-7ddb6cff8997.png)
Save once with the `Apply` button.
Go to the `Table Details` tab.
If you select the table and look at the **Table Name** property, you can see that the SQL statement set earlier is being used.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/53203d0a-4cb0-f394-c555-697d245a0749.png)
Check the table definition and field definition. These are created by the Sharperlight engine based on the SQL statements you provided initially. Mainly check the `Code` and `Description` properties. You can change the value of the properties with what you want, but the `Code` cannot be changed once it has been used.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/46139ce1-e124-9f33-5d47-ae6245555815.png)
After confirming and changing, save the custom table with the `OK` button.

Let's try using the custom table.
Start **Query Builder** from the [Sharperlight](https://sharperlight.com/) application menu.
For `product`, select the product specified when creating the custom table earlier.
Click the Search Tables button to display a list of available tables. You can find the custom table you created earlier in the **Custom Tables** folder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/43e3da91-a5b1-f977-befa-41414cdfb170.png)
This way the field will appear in the **Selection** area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d2ca21aa-7da6-d0c0-d917-ab53fa526e64.png)
Set all fields to the **Outputs** area and run the query with the `Preview` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cd200b06-4e60-1df5-80a8-a802dc6009a7.png)
Data is returned correctly.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/727780de-9b63-afca-97bd-c032ed738052.png)
Let's check what kind of SQL statement is generated.
Select the `!` icon -> SQL statement.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a79eaed8-227a-921f-d408-66e81fa8219e.png)
You can see that the SQL statement set in the custom table is used.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/88993ad2-2636-0b68-33dc-57c6d53ae930.png)
Using custom tables in this way gives you the flexibility to create a solution without making any changes to your existing data model or database.

Thank you.