SQL Source Pane

SQL Source Pane

SQL Source Pane.

When using the UTB and Tables are dropped into the Query Builder Pane the result is built up as an SQL statement in the SQL Source Pane. As table rows and Sort and Select criteria are added the SQL statement is continually developed.

The example above is the SQL auto generated statement for selections made in over two files F411_Sales_Order_Detail_File and F4201_Sales_Order_Header_File.

UTB version 2.0.15 and above now supports Syntax colouring/highlighting in the SQL source pane.  The 'SELECT', 'FROM', 'WHERE' and 'ORDER BY' statements are shown in colour.  Table definitions are shown in black and columns in red.  The WHERE selection criteria - 'VO' and '%M30' in the above example are also shown in colour.

Click the execute button to execute the query or F5 on the keyboard or Query menu option and Execute.

If preferred, you can build your entire query using SQL rather than the Query Builder Pane and Sort and Criteria Panes.  Again, click the execute button to run the query.

Toggle between previously generated/executed SQL statements back and forth by clicking the Previous SQL button and the Next SQL button in the Options Bar.

If using the SQL Source Pane to manually build your query all the standard windows functions are available (i.e. Right click the mouse to use options; Undo, Redo, Cut, Copy, Paste, Select All and Delete).  These functions will be required if you need to define a Column that does not exist in any of the Tables.  The windows keyboard shortcut functions (Ctrl 'C' to Copy etc.) are also available.

For example if the Available Stock On Hand was required from the above F41021_Item_Location_File and was the total on hand less the sum of the hard and soft committed quantities then a new SELECT line would be required:-

       F41021_Item_Location_File.LIPQOH_Quantity_on_Hand - (F41021_Item_Location_File.LIPCOM_Quantity_on_Soft_Commit + F41021_Item_Location_File.LIHCOM_Quantity_on_Hard_Commit) AS Available,

Note: The comma at the end of the SELECT statement is only required if it is not the last SELECT line.