SQL allows expressions in some parts of SQL statements. For example, the following statement multiplies the value in a column by 12:
SELECT monthly_average * 12 FROM table1;
As another example, the following statement uses the built-in SQRT function to calculate the square root of each value in the column named “variance”.
SELECT SQRT(variance) FROM table1;
Our next example uses the “REPLACE” function to convert numbers from U.S. format to European format. In U.S. format, numbers use the period character (.) as the decimal point, but in Europe the comma (,) is used. For example, in the U.S. the approximation of pi is written as “3.14”, while in Europe it is written as “3,14”. We can use the REPLACE function to replace the . character with the , character. The following series of statements shows an example of this.
CREATE TABLE number_strings (n VARCHAR); INSERT INTO number_strings (n) VALUES ('3.14'); -- input in US format. SELECT REPLACE(n, '.', ',') FROM number_strings; -- output in European.
The output looks like
n ---------- 3,14
Note that one function can call another. The following expression takes the square root of a number and then takes the natural log of that square root:
SELECT LOG(SQRT(x)) FROM table1;
solidDB® SQL does not accept completely general expressions in all clauses. For example, in the SELECT clause, you may use predefined functions, but you may not call stored procedures that you have created. Even if you have created a stored procedure named “foo”, the following will not work:
SELECT foo(column1) FROM table1;
When you use expressions, you may want to specify a new name for a column. For example, if you use the expression
SELECT monthly_average * 12 FROM table1;
you probably do not want the output column to be called “monthly_average”. solidDB® server will actually use the expression itself as the name of the column. In this case, the name of the column would be “monthly_average * 12”. That iscertainly descriptive, but for a long expression this can get very messy. You can use the “AS” keyword to give an output column a specific name. In the following example, the output will have the column heading “yearly_average”.
SELECT monthly_average * 12 AS yearly_average FROM table1;
Note that the AS clause works for any output column, not just for expressions. If you like, you may do something like the following:
SELECT ssn AS SocialSecurityNumber FROM table2;
A CASE clause allows you to control the output based on the input. Below is a simple example, which converts a number (1-12) to the name of a month:
CREATE TABLE dates (m INT); INSERT INTO dates (m) VALUES (1); ... INSERT INTO dates (m) VALUES (12);
INSERT INTO dates (m) VALUES (13);
SELECT CASE m WHEN 1 THEN 'January' ... WHEN 12 THEN 'December' ELSE 'Invalid value for month' END AS month_name FROM dates;
Note that this not only allows you to convert valid values, but also allows you to generate appropriate output if there is an error. The “ELSE” clause allows you to specify an alternative value if you get an input value that you were not expecting.
In some situations, you may want to cast a value to a different data type. For example, when inserting BLOB data, it is convenient to create a string that contains your data, and then insert that string into a BINARY column. You may use a cast as shown below:
CREATE TABLE table1 (b BINARY(4)); INSERT INTO table1 VALUES ( CAST('FF00AA55' AS BINARY));
This cast allows you to take data that is a series of hexadecimal digits and input it as though it were a string. Each of the hexadecimal pairs in the quoted string represents a single byte of data. There are 8 hexadecimal digits, and thus 4 bytes of input.
A cast can be used to change output as well as input. In the rather complex code sample below, the expression in the CASE clause converts the output from the format “2003-01-20 15:33:40” to “2003-Jan-20 15:33:40”.
CREATE TABLE sample1(dt TIMESTAMP); COMMIT WORK;
INSERT INTO sample1 VALUES ('2003-01-20 15:33:40'); COMMIT WORK;
SELECT CASE MONTH(dt) WHEN 1 THEN REPLACE(CAST(dt AS varchar), '-01-', '-Jan-') WHEN 2 THEN REPLACE(CAST(dt AS varchar), '-02-', '-Feb-') WHEN 3 THEN REPLACE(CAST(dt AS varchar), '-03-', '-Mar-') WHEN 4 THEN REPLACE(CAST(dt AS varchar), '-04-', '-Apr-') WHEN 5 THEN REPLACE(CAST(dt AS varchar), '-05-', '-May-') WHEN 6 THEN REPLACE(CAST(dt AS varchar), '-06-', '-Jun-') WHEN 7 THEN REPLACE(CAST(dt AS varchar), '-07-', '-Jul-') WHEN 8 THEN REPLACE(CAST(dt AS varchar), '-08-', '-Aug-') WHEN 9 THEN REPLACE(CAST(dt AS varchar), '-09-', '-Sep-') WHEN 10 THEN REPLACE(CAST(dt AS varchar), '-10-', '-Oct-') WHEN 11 THEN REPLACE(CAST(dt AS varchar), '-11-', '-Nov-') WHEN 12 THEN REPLACE(CAST(dt AS varchar), '-12-', '-Dec-') END AS formatted_date FROM sample1;
This takes a value from a column named dt, converts that value from timestamp to VARCHAR, then replaces the month number with an abbreviation for the month (for example, it replaces “-01-” with “-Jan-”). By using the CASE/WHEN/END syntax, we can specify exactly what output we want for each possible input. Note that because this expression is so complicated, it is almost mandatory to use an AS clause to specify the column header in the output.