У реалізаціях мови SQL може бути виконано неявне перетворення типів. Так, наприклад, в T-SQL при порівнянні або комбінуванні значень типів smallint і int. дані типу smallint неявно перетворюються до типу int. Детально про явне і неявному перетворенні типів в MS SQL Server можна прочитати в BOL.
Приклад. Вивести середню ціну ПК-блокнотів з предваряющим текстом "середня ціна =".
Спроба виконати запит
SELECT 'Середня ціна =' + AVG (price) FROM laptop;
приведе до повідомлення про помилку
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
Це повідомлення означає, що система не може виконати неявне перетворення типу varchar до типу money. У подібних ситуаціях може допомогти явне перетворення типів. При цьому, як зазначено в повідомленні про помилку, можна скористатися функцією CONVERT. Ця функція не стандартизована, тому з метою переносимості рекомендується використовувати стандартне вираз CAST. З нього і почнемо.
Якщо переписати наш запит у вигляді
SELECT 'Середня ціна =' + CAST (AVG (price) AS CHAR (15)) FROM laptop;
в результаті отримаємо те, що було потрібно:
Середня ціна = 1410.44
Ми використовували вираз явного перетворення типів CAST для приведення середнього значення ціни до строковому поданням. Синтаксис виразу CAST дуже простий:
CAST (<выражение> AS <тип данных>)
При цьому слід мати на увазі, по-перше, що не будь-які перетворення типів можливі (стандарт містить таблицю допустимих перетворень типів даних). По-друге, результат функції CAST для значення виразу, рівного NULL, теж буде NULL.
Розглянемо ще один приклад. визначити середній рік спуску на воду кораблів з таблиці Ships. запит
SELECT AVG (launched) FROM ships;
дасть результат 1926. В принципі все правильно, тому що ми отримали в результаті те, що просили - РІК. Однак середнє арифметичне становитиме приблизно 1926,2381. Тут слід зазначити, що агрегатні функції (за винятком функції COUNT. Яка завжди повертає ціле число) успадковують тип даних оброблюваних значень. Оскільки поле launched - цілочисельне, ми і отримали середнє значення з відкинутою дробової частиною (наголошую - не округлене).
А якщо нас цікавить результат з заданою точністю, скажімо, до двох десяткових знаків? Застосування вирази CAST до середнього значення нічого не дасть за вказаною вище причини. дійсно,
SELECT CAST (AVG (launched) AS NUMERIC (6,2)) FROM ships;
поверне значення 1926.00. Отже, CAST потрібно застосувати до аргументу агрегатної функції:
SELECT AVG (CAST (launched AS NUMERIC (6,2))) FROM ships;
Результат - 1926.238095. Знову не те. Причина полягає в тому, що при обчисленні середнього значення було виконано неявне перетворення типу. Зробимо ще один крок:
SELECT CAST (AVG (CAST (launched AS NUMERIC (6,2))) AS NUMERIC (6,2)) FROM ships;
В результаті отримаємо те, що потрібно - 1926.24. Однак це рішення виглядає дуже громіздко. Змусимо неявне перетворення типу попрацювати на нас:
SELECT CAST (AVG (launched * 1.0) AS NUMERIC (6,2)) FROM ships;
Тобто ми використовували неявне перетворення цілочисельного аргументу до точного числового типу (EXACT NUMERIC), помноживши його на речову одиницю, після чого застосували явне приведення типу результату агрегатної функції.
Аналогічні перетворення типу можна виконати за допомогою функції CONVERT:
SELECT CONVERT (NUMERIC (6,2), AVG (launched * 1.0)) FROM ships;
Функція CONVERT має наступний синтаксис:
CONVERT (<тип данных>[(<длина>)], <выражение> [, <стиль>])
Основна відмінність функції CONVERT від функції CAST полягає в тому, що перша дозволяє форматувати дані (наприклад, темпоральні дані типу datetime) при перетворенні їх до символьного типу і вказувати формат при зворотному перетворенні. Різні цілочисельні значення необов'язкового аргументу стиль відповідають певним форматам. Розглянемо наступний приклад
Тут ми перетворимо строкове представлення дати до типу datetime. після чого виконуємо зворотне перетворення, щоб продемонструвати результат форматування. Оскільки значення аргументу стиль не задано, використовується значення за замовчуванням (0 або 100). В результаті отримаємо
Нижче наведені деякі інші значення аргументу стиль і результат, отриманий на наведеному вище прикладі. Зауважимо, що значення стиль великі 100 призводять до чотиризначний відображенню року.
Оператор CASE може бути використаний в одній з двох синтаксичних форм записи:
1-я форма
CASE <проверяемое выражение>
WHEN <сравниваемое выражение 1>
THEN <возвращаемое значение 1>
...
WHEN <сравниваемое выражение N>
THEN <возвращаемое значение N>
[ELSE <возвращаемое значение>]
END
2-я форма
CASE
WHEN <предикат 1>
THEN <возвращаемое значение 1>
...
WHEN <предикат N>
THEN <возвращаемое значение N>
[ELSE <возвращаемое значение>]
END
Всі пропозиції WHEN повинні мати однакову синтаксичну форму, тобто не можна змішувати першу і другу форми. При використанні першої синтаксичної форми умова WHEN задовольняється, як тільки значення перевіряється виразу стане рівним значенню виразу, вказаного в реченні WHEN. При використанні другої синтаксичної форми умова WHEN задовольняється, як тільки предикат приймає значення TRUE. При задоволенні умови оператор CASE повертає значення, вказане у відповідному реченні THEN. Якщо жодна з умов WHEN не виповнилося, то буде використано значення, вказане в пропозиції ELSE. При відсутності ELSE. буде повернуто NULL-значення. Якщо задоволені кілька умов, то буде повернуто значення пропозиції THEN першого з них.
У наведеному вище прикладі була використана друга форма оператора CASE.
Зауважимо, що для перевірки на NULL стандарт пропонує більш коротку форму оператора - COALESCE. Цей оператор має довільне число параметрів і повертає значення першого, відмінного від NULL. Для двох параметрів оператор COALESCE (A, B) еквівалентний наступному оператору CASE.
CASE WHEN A IS NOT NULL THEN A ELSE B END
Рішення розглянутого вище прикладу при використанні оператора COALESCE можна переписати таким чином:
SELECT DISTINCT product.model,
COALESCE (CAST (price as CHAR (20)), 'Ні в наявності') price
FROM product LEFT JOIN pc c ON product.model = c.model
WHERE product.type = 'pc';
Використання першої синтаксичної форми оператора CASE можна продемонструвати на наступному прикладі. Вивести всі наявні моделі ПК із зазначенням ціни. Відзначити найдорожчі і найдешевші моделі.
SELECT DISTINCT model, price,
CASE price WHEN (SELECT MAX (price) FROM pc) THEN 'Найдорожчий'
WHEN (SELECT MIN (price) FROM pc) THEN 'Найдешевший'
ELSE 'Середня ціна' END comment
FROM pc ORDER BY price;
В результаті виконання запиту отримаємо