proposalbot
78ac771d84
DDM UMN 1st v Reviewed-by: Wagner, Fabian <fabian.wagner@t-systems.com> Co-authored-by: proposalbot <proposalbot@otc-service.com> Co-committed-by: proposalbot <proposalbot@otc-service.com>
112 lines
13 KiB
ReStructuredText
112 lines
13 KiB
ReStructuredText
:original_name: ddm_03_0063.html
|
|
|
|
.. _ddm_03_0063:
|
|
|
|
Functions
|
|
=========
|
|
|
|
Supported Functions
|
|
-------------------
|
|
|
|
.. table:: **Table 1** Operator functions
|
|
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| Expression | Example |
|
|
+===============+========================================================================================================================================================================+
|
|
| IN | SELECT \* FROM Products WHERE vendor_id IN ( 'V000001', 'V000010' ) ORDER BY product_price |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| NOT IN | SELECT product_id, product_name FROM Products WHERE vendor_id NOT IN ('V000001', 'V000002') ORDER BY product_id |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| BETWEEN | SELECT id, product_id, product_name, product_price FROM Products WHERE id BETWEEN 000005 AND 000034 ORDER BY id |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| NOT...BETWEEN | SELECT product_id, product_name FROM Products WHERE NOT vendor_id BETWEEN 'V000002' and 'V000005' ORDER BY product_id |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| IS NULL | SELECT product_name FROM Products WHERE product_price IS NULL |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| IS NOT NULL | SELECT id, product_name FROM Products WHERE product_price IS NOT NULL ORDER BY id |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| AND | SELECT \* FROM Products WHERE vendor_id = 'V000001' AND product_price <= 4000 ORDER BY product_price |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| OR | SELECT \* FROM Products WHERE vendor_id = 'V000001' OR vendor_id = 'V000009' |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| NOT | SELECT product_id, product_name FROM Products WHERE NOT vendor_id = 'V000002' |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| LIKE | SELECT \* FROM Products WHERE product_name LIKE 'NAME%' ORDER BY product_name |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| NOT LIKE | SELECT \* FROM Products WHERE product_name NOT LIKE 'NAME%' ORDER BY product_name |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| CONCAT | SELECT product_id, product_name, CONCAT( product_id , '(', product_name ,')' ) AS product_test FROM Products ORDER BY product_id |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| |image2| | SELECT 3 \* 2+5-100/50 |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| ``-`` | SELECT 3 \* 2+5-100/50 |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| \* | SELECT order_num, product_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num BETWEEN 000009 AND 000028 ORDER BY order_num |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| / | SELECT 3 \* 2+5-100/50 |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| UPPER | SELECT id, product_id, UPPER(product_name) FROM Products WHERE id > 10 ORDER BY product_id |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| LOWER | SELECT id, product_id, LOWER(product_name) FROM Products WHERE id <= 10 ORDER BY product_id |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| SOUNDEX | SELECT \* FROM Vendors WHERE SOUNDEX(vendor_name) = SOUNDEX('test') ORDER BY vendor_name |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| IFNULL | SELECT IFNULL(product_id, 0) FROM Products; |
|
|
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
.. table:: **Table 2** Time and date functions
|
|
|
|
+-----------------------------------+------------------------------------------------------+
|
|
| Expression | Example |
|
|
+===================================+======================================================+
|
|
| DAY() | SELECT \* FROM TAB_DATE WHERE DAY(date)=21 |
|
|
| | |
|
|
| | SELECT \* FROM TAB_DATE WHERE date='2018-12-21' |
|
|
| | |
|
|
| | INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22') |
|
|
+-----------------------------------+------------------------------------------------------+
|
|
| MONTH() | SELECT \* FROM TAB_DATE WHERE MONTH(date)=12 |
|
|
| | |
|
|
| | SELECT \* FROM TAB_DATE WHERE date='2018-12-21' |
|
|
| | |
|
|
| | INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22') |
|
|
+-----------------------------------+------------------------------------------------------+
|
|
| YEAR() | SELECT \* FROM TAB_DATE WHERE YEAR(date)=2018 |
|
|
| | |
|
|
| | SELECT \* FROM TAB_DATE WHERE date='2018-12-21' |
|
|
| | |
|
|
| | INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22') |
|
|
+-----------------------------------+------------------------------------------------------+
|
|
|
|
.. table:: **Table 3** Mathematical functions
|
|
|
|
+------------+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| Expression | Example |
|
|
+============+=============================================================================================================================+
|
|
| SQRT() | SELECT id, product_price, SQRT(product_price) AS price_sqrt FROM Products WHERE product_price < 4000 ORDER BY product_price |
|
|
+------------+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| AVG() | SELECT AVG(product_price) AS avg_product FROM Products |
|
|
+------------+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| COUNT() | SELECT COUNT(``*``) AS num_product FROM Products |
|
|
+------------+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| MAX() | SELECT id, product_id, product_name, MAX(product_price) AS max_price FROM Products ORDER BY id |
|
|
+------------+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| MIN() | SELECT id, product_id, product_name, MIN(product_price) AS min_price FROM Products ORDER BY id |
|
|
+------------+-----------------------------------------------------------------------------------------------------------------------------+
|
|
| SUM() | SELECT SUM(product_price) AS sum_product FROM Products |
|
|
+------------+-----------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
Unsupported Functions
|
|
---------------------
|
|
|
|
.. table:: **Table 4** Function restrictions
|
|
|
|
=========== ==========================================
|
|
Item Restriction
|
|
=========== ==========================================
|
|
ROW_COUNT() Function **ROW_COUNT()** is not supported.
|
|
=========== ==========================================
|
|
|
|
.. |image1| image:: /_static/images/en-us_image_0000001749511672.png
|
|
.. |image2| image:: /_static/images/en-us_image_0000001749511672.png
|