Funciones de Conversión-(Funciones de una Fila SQL, Parte 3)

Objetivos: 

•Describir las Conversiones Implícitas y Explicitas de SQL.

•Describir el uso de las funciones de conversión.
•Describir el uso de las funciones generales de SQL ORACLE.
•Describir las Expresiones Condicionales.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

_____________________________________________________________________________________

Funciones de Conversion.

Las funciones de conversión convierten un valor de un tipo de dato a otro. Ahora bien ORACLE SERVER tiene un mecanismo interno de conversion implícita el cual funciona así:

Conversiones Implícitas:

•Si X columna, variable, función, etc espera un tipo de dato y recibe otro diferente, ORACLE SERVER lo convierte implícitamente siempre y cuando sea posible. La siguiente tabla muestra las conversiones implícitas posibles:

De	A
VARCHAR2 o CHAR	NUMBER
VARCHAR2 o CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

•Repito, ORACLE SERVER hace dichas conversiones siempre y cuando sea posible, osea, la conversion de VARCHAR2 a NUMBER es posible si la cadena de caracteres representa un número válido. Eje: salary = '12000'. De igual manera la Conversion de VARCHAR2 a  DATE es posible si la cadena de caracteres representa una fecha valida. Eje: hire_date = '12-jun-06'. El caso de la conversiones de NUMBER o DATE a VARCHAR2 son siempre posibles ya que cualquier cadena de caracteres puede ser VARCHAR2 : '695*/,.%32@'.

_____________________________________________________________________________________

Conversiones Explicitas.

•Como no siempre las conversiones Implícitas son posibles y también cuando se da la necesidad de convertir los datos con un formato especifico es requerido utilizar las Funciones de Conversion disponibles en ORACLE SQL.

---

Función: TO_CHAR.

Sintaxis: TO_CHAR(number|date,[ fmt], [nlsparams])

Descripción: Convierte un valor numérico o de fecha a una cadena de caracteres VARCHAR2 con el modelo de formato fmt(opcional). Conversión de fecha: El parámetro nlsparams(opcional) especifica el lenguaje en que se devolverán los nombres y las abreviaturas de mes y día, Si se omite este parámetro, esta función utiliza los lenguajes de fecha por defecto para la sesión. Conversión numérica: El parámetro nlsparams especifica los siguientes caracteres, que son devueltos por elementos de formato numérico:

•Carácter decimal

•Separador de grupos

•Símbolo de divisa local

•Símbolo de divisa internacional, Si se omite nlsparams o cualquier otro parámetro, esta función utiliza los valores de parámetros por defecto para la sesión.

---

Función: TO_NUMBER.

Sintaxis: TO_NUMBER(char,[fmt], [nlsparams])

Descripción:  Convierte una cadena de caracteres que contenga dígitos en un número con el formato especificado por el modelo de formato opcional fmt. El parámetro nlsparams tiene el mismo objetivo en esta función que en la función TO_CHAR de conversión numérica.

---

Función: TO_DATE.

Sintaxis: TO_DATE(char,[fmt], [nlsparams])

Descripción: Convierte una cadena de caracteres que representa una fecha en un valor de fecha de acuerdo con el fmtque se haya especificado. Si se omite fmt, el formato es DD-MON-YY. El parámetro nlsparams tiene el mismo objetivo en esta función que en la función TO_CHAR de conversión de fecha.

Nota: Las funciones mencionadas anteriormente únicamente suponen una parte de las funciones de conversión disponibles. Para una mayor lista consulte “Conversion Functions” en Oracle SQL Reference.

_____________________________________________________________________________________

Uso Función TO_CHAR.

Uso de TO_CHAR con fechas.

Esta función es ideal para mostrar la fecha en un formato diferente al definido por defecto en la Base de Datos.

•El modelo de formato debe ir entre comillas simples y es sensible a mayúsculas/minúsculas.

•El modelo de formato puede incluir cualquier elemento de formato de fecha. Asegúrese de separar el valor de fecha del modelo de formato con una coma.

•Los nombres de días y meses de la salida se rellenan automáticamente con espacios en blanco.

•Para eliminar espacios en blanco rellenados o para suprimir ceros iniciales, utilice el elemento fm del modo de relleno.

Algunos elementos útiles para el formato de Fechas.

Elemento	Resultado
YYYY	Año completo con números
YEAR	Nombre completo del año con letras (en inglés)
MM	Valor de dos dígitos para el mes
MONTH	Nombre completo del mes
MON	Abreviatura de tres letras del mes
DY	Abreviatura de tres letras del día de la semana
DAY	Nombre completo del día de la semana
DD	Día del mes con números
TH	Número ordinal (por ejemplo, DDTH para 11TH)
SP	Número completo con letras (por ejemplo, DDSP para EIGHT
SPTH o THSP	Número ordinal completo con letras (por ejemplo, DDSPTH para FIFTEENTH
AM o PM	Indicador de meridiano
A.M. o P.M..	Indicador de meridiano, con puntos
HH o HH12 o HH24	Hora del día, u hora (1–12) u hora (0–23)
/  .  ,	La puntuación se reproduce en el resultado.
“de”	Lo que esta dentro de las comillas se reproduce en el resultado.

Ejemplos:

SELECT 

    TO_CHAR(SYSDATE,'fmDay, Month DD "of" YYYY') "Fecha en Ingles con fm",

    TO_CHAR(SYSDATE,'DAY, MONTH DD "of" YYYY') "Fecha en Ingles sin fm",

    TO_CHAR(SYSDATE+4,'DY, DD/MON/YY') "Fecha+4 Abreviada",

    TO_CHAR(SYSDATE,'Month.YYYY') ". Mes y Año sin fm",

    TO_CHAR(SYSDATE,'fmDay dd "de" Month','NLS_DATE_LANGUAGE=SPANISH') "Dia y Mes en Español"

FROM DUAL;

/*En el Query anterior la primera columna muestra el Día y Mes en letras, el literal "of" y el año en dígitos, algunos espacios son removidos con fm; La 2da columna muestra lo mismo pero sin fm; En la 3ra columna se le suma 4  días a la fecha y se muestra abreviada; La 4ta columna muestra solo el Mes y Año sin fm y la ultima muestra el Día y Mes en Español con fm. Como Nota: nlsparams y fmt son opcionales*/

---

SELECT 

    TO_CHAR(SYSDATE,'FMDAY DDth') "Ejemplo con TH",

    TO_CHAR(SYSDATE,'Day Ddthsp') "Ejemplos con THSP",

    TO_CHAR(SYSDATE,'HH12:MI:SS:SSSS PM') "Hora HH12, SSSS y Meriado ",

    TO_CHAR(SYSDATE,'HH24:MI:SS AM') "Hora HH24, Meriano y sin SSSS"

FROM DUAL;

/*El Query anterior muestra el Día actual y su equivalente en numero ordinal abreviado(Col 1); El Día y su equivalente ordinal en letras(Col 2); La hora en formato 12 horas, minutos, segundos,  milésimas de segundos y el meridiano (Col 3); La hora en formato 24 horas, minutos, segundos y meridiano.*/

---

Uso de TO_CHAR con Números.

Existen escenarios en los cuales es requerido dar un formato X a una cifra numérica, en esos casos nos valemos de la función TO_CHAR para así lograr el cometido. 

Es bueno tener en cuenta que:
•Oracle Server muestra una cadena de signos numéricos (#) en lugar de un número completo cuyos dígitos excedan el número de dígitos que se proporciona en el modelo de formato.
•Oracle Server redondea el valor decimal almacenado al número de posiciones decimales que se proporciona en el modelo de formato.

Algunos elementos útiles para el formato de Números.

Elemento	Resultado
9	Representa un número
0	Muestra ceros
$	Coloca un signo de dólar flotante
L	Utiliza el símbolo de divisa local flotante
.	Imprime un punto decimal
,	Imprime una coma como indicador de miles

Ejemplo:
SELECT
    TO_CHAR(19999999.99,'fm$9,999,999.00') AS "Muestra #",
    TO_CHAR(1999999.99,'fm$9,999,999.00') AS "Muestra Cifra",
    TO_CHAR(199999.9,'fm$9,999,999.00') AS "Muestra Cifra 2",
    TO_CHAR(19999.999,'fm$9,999,999.00') AS "Redondea",
    TO_CHAR(199.333,'fm$9,999,999.00') AS "Redondea 2"
FROM DUAL;

/*En la anterior consulta usamos el mismo formato con diferentes cantidades para así poder apreciar como funciona el TO_CHAR  con números, la primera cifra tiene un dígito entero mas que el modelo de formato, por lo cual el resultado es una cadena de '#'; la 2da cifra es mostrada como se espera con las comas y punto en el lugar esperado; en la 3ra todo sale bien como en la 2ra; en la 4ta la cifra tiene un decimal mas que el propuesto en el modelo de formato, por lo cual dicha cifra es redondeada; en la ultima también la cifra es redondeada.*/

_____________________________________________________________________________________

Uso Funciones TO_NUMBER y TO_DATE.

De la function TO_NUMBER no hay mucho que decir, ella transforma una cadena de carácter que de alguna manera representa una cifra numérica a números simples, uno de sus usos mas comunes es el de validar si un campo o expresión contiene dígitos. Es mas común el uso de TO_DATE, la cual nos ayuda a dar formato a una cadena de carácter que tiene un formato de fecha diferente al que tenemos en la Base de Datos o diferente al que queremos mostrar.

Sintaxis

TO_NUMBER(char[, 'format_model'])

TO_DATE(char[, 'format_model'])

Descripción: El modificador fx especifica la correspondencia exacta del argumento de carácter y el modelo de formato de fecha de una función TO_DATE.

•La puntuación y el texto entre comillas del argumento de carácter debe corresponder exactamente (excepto en las mayúsculas/minúsculas) con las partes correspondientes del modelo de formato.

•El argumento de carácter no puede contener espacios en blanco adicionales. Sin fx, Oracle ignora los espacios en blanco adicionales.
•Los datos numéricos del argumento de carácter debe tener el mismo número de dígitos que el elemento correspondiente del modelo de formato. Sin fx, los números del argumento de carácter pueden omitir los ceros iniciales.
Ejemplos:
SELECT
     TO_NUMBER('$1,845.5','$999,999,999.00') "TO_NUMBER",
     TO_NUMBER('$741,881,845','$999,999,999.00') "TO_NUMBER",
     TO_NUMBER('$41,845.99','$999,999,999.00') "TO_NUMBER"
FROM DUAL;
/*Supongamos que tengamos algunas cantidades numéricas con un formato X en forma de string y debamos almacenar dichas cifras en la base de datos pero en su forma numérica, el query anterior muestra el uso de TO_NUMBER.*/
---
SELECT 
    TO_DATE('2017-18-08','YYYY-DD-MM') AS "FECHA 1",
    TO_DATE('17.1.2018','DD.MM.YYYY') AS "FECHA 2",
    TO_DATE('MAY 17, 2014','MONTH DD, YYYY') AS "FECHA 3",
    TO_DATE('2,JUNE,2019','DD,MONTH,YYYY') AS "FECHA 4"
FROM   DUAL;
/*Como Apreciamos en el ejemplo anterior, TO_DATE toma una cadena de caracteres con un formato de fecha valido pero diferente al de la Base de Datos y la adecua a ella.*/

_____________________________________________________________________________________

Anidando Funciones.

La Técnica de anidar funciones es Vital en en desarrollo y mantenimiento de procesos en una Base de Datos, esto nos permite obtener resultados claves en diferentes consultas comunes del día a día, por ello es necesario tener claro su uso y tratar de familiarizarse lo mas que podamos con ella.

Como Funciona esta técnica:
•Las funciones anidadas se evalúan desde el nivel más interno al más externo.

•Las funciones de una sola fila se pueden anidar hasta cualquier profundidad.

Ejemplo:

SELECT LAST_NAME, JOB_ID,

       UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 3), SUBSTR(JOB_ID,INSTR(JOB_ID,'_'),5))),

       LENGTH(UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 3), SUBSTR(JOB_ID,INSTR(JOB_ID,'_'),5))))

FROM employees;

/*En el ejemplo anterior mostramos el nombre(LAST_NAME) y puesto(JOB_ID) de cada empleado, luego en la 3ra columna determinamos(INSTR ) la posición inicial del carácter '_' en la cadena JOB_ID y a partir de esa posición retornamos(SUBSTR) 5 caracteres del mismo JOB_ID, eso es concatenado(CONCAT) con los tres primeros caracteres del nombre y por último todo esto es convertido(UPPER) a letras MAYÚSCULAS, en la 4ta columna se muestra la cantidad(LENGTH) de caracteres que tiene la 3ra columna.*/

_____________________________________________________________________________________

Algunas Funciones Generales.

Estas funciones pueden utilizar cualquier tipo de datos y están relacionadas con el uso de valores nulos: 

Función NVL.
Sintaxis
NVL(expr1, expr2)

Descripción:

•Convierte un valor nulo en un valor real.

•Los parámetros pueden ser de cualquier tipo de dato pero deben corresponder entre ellos, entiéndase que ambos deben ser del mismo tipo.
•expr1 es el valor o la expresión de origen que puede contener un valor nulo.
•expr2 es el valor de retorno si expr1 es nulo.

•Como retorna uno de los parámetros, el valor retornado es del mismo tipo que ellos.

Ejemplo:

SELECT EMPLOYEE_ID AS COL1 ,

    NVL(MANAGER_ID,0) AS COL2,

    NVL(TO_CHAR(COMMISSION_PCT),'No Comision') AS COL3,

    NVL(HIRE_DATE,SYSDATE) AS COL4

FROM HR.EMPLOYEES

WHERE EMPLOYEE_ID IN (204,145,100);

/*El anterior ejemplo muestra el código de empleado(COL1 ) , el código de su manager(COL2) y si no tiene manager muestra 0 como es el caso del emp#: 100, la comisión(COL3) que gana y si no tiene muestra 'No Comisión'(la comisión fue convertida a CHAR para satisfacer la Función NVL) y finalmente se muestra la fecha de contratación(COL4) de los empleado 204,145 y 100.*/

--

Función NVL2.
Sintaxis
NVL2(expr1, expr2, expr3)

Descripción:

•La función NVL2 examina la primera expresión. Si la primera expresión no es nula, la función NVL2 devuelve la segunda expresión. Si la primera expresión es nula, se devuelve la tercera expresión.
•expr1 es el valor o la expresión de origen que puede contener un valor nulo.
•expr2 es el valor que se devuelve si expr1 no es nulo.
•expr3 es el valor que se devuelve si expr2 es nulo.

Nota: El argumento expr1 puede tener cualquier tipo de datos. Los argumentos expr2 y expr3 pueden tener cualquier tipo de datos excepto LONG. Si los tipos de datos de expr2 y expr3 son diferentes, Oracle Server convierte expr3 al tipo de datos de expr2 antes de compararlos a menos que expr3 sea una constante nula. En el último caso, no es necesaria una conversión del tipo de datos. El tipo de datos del valor de retorno es siempre el mismo que el tipo de datos de expr2, a menos que expr2 sean datos de carácter, en cuyo caso el tipo de datos del valor de retorno es VARCHAR2.

Ejemplo:

SELECT last_name, salary, commission_pct,
         NVL2(commission_pct, 'SAL+COMM', 'SAL') income
FROM employees 

WHERE department_id IN (50, 80);

/*En el pasado ejemplo mostramos el nombre salario y comisión de los empleados de los departamentos 50 y 80, para los empleados que ganan comisión la 4ta columna mostrara: 'SAL+COMM' y para los que no mostrara: 'SAL.'*/

---
Función NULLIF.
Sintaxis
NULLIF (expr1, expr2)
Descripción: 

•La función NULLIF compara dos expresiones. Si son iguales, la función devuelve un valor nulo. Si diferentes, la función devuelve la primera expresión. 

•expr1 es el valor de origen que se compara con expr2.

•expr2 es el valor de origen que se compara con expr1 (Si no es igual que expr1, se devuelve expr1.).

•No puede especificar el literal NULL para la primera expresión(expr1 ).

Ejemplo:

SELECT 

    first_name, LENGTH(first_name) "expr1", 

    last_name, LENGTH(last_name) "expr2",

    NULLIF(LENGTH(first_name), LENGTH(last_name)) result 

FROM hr.employees;

/*En el ejemplo anterior se muestra el nombre seguido de la cantidad de caracteres que lo compone, luego se hace lo mismo con el apellido y por último se evalúan(NULLIF) los tamaños de ambas cadenas, los empleados con la misma cantidad de caracteres en el nombre y apellido muestran null, en los que no, se muestra el tamaño del nombre.*/

---

Función COALESCE.
Sintaxis
COALESCE (expr1, expr2, ... exprn)

Descripción: 

•Si la primera expresión no es nula, la función COALESCE devuelve esa expresión; en caso contrario, realiza una fusión (COALESCE) de las expresiones restantes.

•expr1 devuelve esta expresión si no es nula.

•expr2 devuelve esta expresión si la primera expresión es nula y esta expresión no lo es.

•exprn devuelve esta expresión si las expresiones precedentes son nulas.

•Todas las expresiones deben ser del mismo tipo de datos.
•Si todos los parámetros son Nulos, la función retorna NULL.
Ejemplo
SELECT commission_pct, manager_id,
       COALESCE(commission_pct, manager_id, -1) comm
FROM hr.employees
ORDER BY manager_id DESC, commission_pct DESC;

/*En el ejemplo anterior, si el valor COMMISSION_PCT no es nulo, se muestra en la ultima columna(comm). Si el valor COMMISSION_PCT es nulo, se muestra MANAGER_ID. Si los valores MANAGER_ID y COMMISSION_PCT son nulos, se muestra el valor –1.*/

_____________________________________________________________________________________

Expresiones Condicionales.

•Permiten utilizar la lógica IF-THEN-ELSE dentro de una sentencia SQL

•Los dos métodos utilizados para implementar procesamiento condicional (lógica IF-THEN-ELSE) en una sentencia SQL son la expresión CASE y la función DECODE. 

Nota: La expresión CASE cumple con ANSI SQL. La función DECODE es específica de la sintaxis Oracle.

Expresión Case.
Sintaxis
---CASE simple.
CASE expr WHEN comparison_expr1 THEN return_expr1
                  [WHEN comparison_expr2 THEN return_expr2
                    WHEN comparison_exprn THEN return_exprn
                    ELSE else_expr]
END

---CASE buscada.

CASE WHEN expr  '!' comparison_expr1 THEN return_expr1

          [WHEN expr  '!' comparison_expr2 THEN return_expr2

           WHEN expr  '!' comparison_exprn THEN return_exprn

           ELSE else_expr]
END

Descripción:

• '!' Entiéndase como un operador de comparación cualquiera.

•Las expresiones CASE le permiten utilizar la lógica IF-THEN-ELSE en sentencias SQL sin llamar a procedimientos. 

•En una expresión CASE simple, Oracle Server busca el primer par WHEN ... THEN en el que expr sea igual a comparison_expr y devuelve return_expr. Si ninguno de los pares WHEN ... THEN cumplen esta condición y si existe una cláusula ELSE, Oracle Server devuelve else_expr. De lo contrario, Oracle Server devuelve un valor nulo. No puede especificar el literal NULL para todas las expresiones return_exprs y else_expr. 

•En una expresión CASE buscada, la búsqueda se produce de izquierda a derecha hasta que se encuentre una incidencia de la condición mostrada y, entonces, devuelve la expresión de retorno. Si no se encuentra ninguna condición que sea verdadera y si existe una cláusula ELSE, se devuelve la expresión de retorno de la cláusula ELSE; de lo contrario, devuelve NULL.

•Todas las expresiones ( expr, comparison_expr y return_expr) deben ser del mismo tipo de datos, que puede ser CHAR, VARCHAR2, NCHAR o NVARCHAR2.

Ejemplos.

SELECT last_name, job_id, salary,

       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary 

                              WHEN 'ST_CLERK' THEN  1.15*salary 

                              WHEN 'SA_REP'   THEN  1.20*salary 

                              ELSE salary 

        END   AS  "REVISED SALARY" 

FROM hr.employees; 

/*En el Query anterior se evalúa el puesto de trabajo(job_id ) de cada empleado. Si es 'IT_PROG', se le suma 10% al salario; si es 'ST_CLERK', se le suma 15%; si es 'SA_REP', se le suma 20%. Finalmente si no es ninguno se ellos se mantiene el mismo salario.*/

---

SELECT last_name,salary,

  (CASE WHEN salary<5000 THEN 'Salario Bajo'

               WHEN salary<10000 THEN 'Salario Regular'

               WHEN salary<20000 THEN 'Buen Salario'

                ELSE 'This guy is Rich'

END) AS "Calidad Salario"

FROM hr.employees;

/*En el Query anterior se evalúa el salario de cada empleado. Si es menor que 5000, nuestra columna "Calidad Salario" muestra: 'Salario Bajo'; si es menor que 10000 muestra: 'Salario Regular'; si es menor que 20000, muestra:  'Buen Salario'. Si no cumple ninguna de las condiciones anteriores muestra: 'This guy is Rich'*./

--

Función Decode.

Sintaxis

DECODE(col|expression, search1, result1 

        [, search2, result2,...,]

        [, default])

Descripción:

•La función DECODE descodifica una expresión de forma parecida a la lógica IF-THEN-ELSE o CASE que se utilizan en varios lenguajes.

•La función DECODE descodifica la expression tras compararla con cada valor search. Si la expresión es igual que search, se devuelve result.
•Si se omite el valor por defecto(default), se devuelve un valor nulo donde un valor de búsqueda no corresponda a ninguno de los valores del resultado.

Ejemplos.

SELECT last_name, job_id, salary,

       DECODE(job_id, 'IT_PROG',  1.10*salary, 

                                      'ST_CLERK', 1.15*salary, 

                                      'SA_REP',   1.20*salary, 

                                       salary) AS "REVISED SALARY" 

FROM hr.employees; 

/*Este ejemplo es el mismo primer ejemplo de la Expresión CASE, pero ahora con DECODE.*/

---

SELECT last_name, salary, TRUNC(salary/2000),

       DECODE(TRUNC(salary/2000, 0), 

                                                0, 0.00, 

                                                1, 0.09, 

                                                2, 0.20, 

                                                3, 0.30, 

                                                4, 0.40, 

                                                5, 0.42, 

                                                6, 0.44, 

                                                   0.45) AS "Impuesto Salarial"

FROM hr.employees 

WHERE department_id = 80; 

/*En este ejemplo, determinamos la tasa de impuestos para cada empleado del departamento 80 basándonos en el salario mensual. Las tasas de impuestos son:

Rango de Salarios Mensuales	Tasa de Impuestos
$0.00–1,999.99	0%
$2,000.00–3,999.99	9%
$4,000.00–5,999.99	20%
$6,000.00–7,999.99	30%
$8,000.00–9,999.99	40%
$10,000.00–11,999.99	42%
$12,200.00–13,999.99	44%
$14,000.00 o más	45%

*/

_____________________________________________________________________________________

Con esta 3ra Parte, terminamos las Funciones de una Fila SQL.

====>>Parte 1

---

====>>Parte 2

_____________________________________________________________________________________

Link con un Archivo de ejercicios de práctica!!!

_____________________________________________________________________________________

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1