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;
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 all expressions in all clauses. For example, in the SELECT clause, you can use predefined functions, but you cannot call stored procedures that you have created. Even if you have created a stored procedure named foo, the following statement will not work:
SELECT foo(column1) FROM table1;
REPLACE function
You can use the REPLACE function to convert numbers from one format to another format. In some countries, the period character (.) is used as the decimal point in numbers, but in other countries, the comma (,) is used. For example, the approximation of pi might be written as 3.14 or 3,14. You can use the REPLACE function to replace the ‘.’ character with the ‘,’ character as shown in the following example.
When you use expressions, you might want to specify a new name for a column. For example, if you use the following expression:
SELECT monthly_average * 12 FROM table1;
then, by default, solidDB uses the expression itself as the name of the column; in this case, the name of the column would be monthly_average * 12. However, 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. For example, you can have something like the following statement:
SELECT ssn AS SocialSecurityNumber FROM table2;
CASE clause
A CASE clause allows you to control the output based on the input. The following example 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.
CAST function
In some situations, you might 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 might use a cast as shown in the following code:
CREATE TABLE table1 (b BINARY(4)); INSERT INTO table1 VALUES ( CAST('FF00AA55' AS BINARY));
This cast allows you to input a series of hexadecimal digits as though it was 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 following code sample, 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;
The code 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, you can specify exactly what output that you want for each possible input.