The Columns pane

The Columns pane is responsible for many factors involved in the creation of queries via such visual interface (see Figure 1.8 for more details). Some of these factors are as follows:

·        Selecting fields in the SQL statement,

·        Selecting field-columns, on which SQL functions may be applied,

·        Defining query criteria, sorting and grouping on fields easily accessible via combo-box controls,

As well as other factors which together with the Relations and SQL pane simplifies the creation of complex queries.


Figure 1.8. Showing the Columns pane with the SQL functions toolbar

The SQL functions toolbar contains all SQL functions necessary to perform advance queries. This toolbar is an indispensable tool for novice users that frequently need SQL language books or help files to write SQL commands. Furthermore, this tool universalizes the SQL language so that users may create advance queries in Oracle, even if they only know MS-Access or MS-SQL Server (or visa versa). Essentially the SQL functions are categorized into buttons, with each button being a combo-box listing related SQL functions (see Figure 1.9). We list next these combo-box buttons in the order from left to right:

·       Functions for concatenation of fields into string,

·       Functions for masking concatenated fields,

·       Aggregate functions including: AVG, COUNT, MAX, MIN, SUM, STDEV, VAR,

·       String functions including: CHARINDEX, DIFFERENCE, LEFT, LEN, LOWER, LTRIM, PATINDEX, REPLACE, QUOTENAME, REPLICATE, LEFTPAD, RIGHTPAD, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, STR, STUFF, SUBSTR, TRIM, UPPER,

·       Numeric functions including: ABS, CEILING, FLOOR, ROUND, TRUNCATE, SIGN,

·       Date part functions including: YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, MILLISECOND,

·       Date name functions including: YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, MILLISECOND,

·       Conversion to char functions,

·       Conversion to number functions,

·       Conversion to date functions,

·       Null case function,

·       Simple case function,


Figure 1.9. Showing the concatenation to string options of the SQL functions toolbar