Problema con resultados Null

[vpepen]

Hola foro:

Tengo dos tablas con la siguiente estructura:

Tabla Cuenta:
Numero
Nombre

Tabla Movimientos:
Fecha
Cod_Cta
Tipo_Mov // 'D' -> Débito, 'C' -> Crédito
Monto

Necesito realizar una consulta que permita mostrar todas las cuentas y sus respectivos balances, para lo cual he realizado lo siguiente:

SELECT CODIGO, NOMBRE, (SELECT SUM(MONTO) FROM MOVIMIENTOS
WHERE COD_CTA = CUENTA.CODIGO AND TIPO_MOV='D') -
(SELECT SUM(MONTO) FROM MOVIMIENTOS WHERE COD_CTA = CUENTA.CODIGO AND TIPO_MOV='C')
FROM CUENTA;

Todo marcha de maravillas hasta que una de las subconsultas devuelve un valor null, es decir, no hay movimientos de ese tipo (por ejemplo: no dispone de débito o crédito), en tal caso el resultado siempre será null, ya que según veo valor-null = null, null-valor = null.

Nota: Estoy usando InterBase 6.

Las gracias anticipadas.

Víctor.-

[Neftali [Germán.Estévez]]

Creo que las JOIN te pueden resolver el problema. Revisa la documentación y busca INNER JOIN, LEFT JOIN y RIGHT JOIN; Posiblemente la misma consulta utilizando JOIN te resuelva el problema.

Si no te acalaras ya dirás....

[vpepen]

Neftali...

Leí la documentación de las join, left join y rigth join y la verdad que no veo cómo podría resolver el problema con ellas.

Gracias por la pronta respuesta,

Víctor.-

[frudolph]

Como bien dice Neftali, la cláusula JOIN debería servir.

Tu consulta debería quedar más o menos así:

SELECT c.codigo, c.nombre, sum(m1.monto) AS debito, sum(m2.monto) AS credito
FROM cuenta c
LEFT JOIN movimientos m1 ON m1.cod_cta = c.codigo AND m1.tipo_mov = 'D'
LEFT JOIN movimientos m2 ON m2.cod_cta = c.codigo AND m2.tipo_mov = 'C'

En caso de que las cuentas no tengan movimientos, las columnas de resultado "debito" y "credito" devolverán un valor NULL, pero NO faltará ningún registro de la tabla "cuenta".

[vpepen]

Hola frudolph,

Creo que al parecer no he sabido explicar bien el problema. La situación es la siguiente: He podido mostrar el total de débidos y créditos por separados de cada cuenta, por ejemplo:
______________________________________________________
Codigo | Nombre | Debidos | Credito |
______________________________________________________

El problema se presenta cuando quiero incluir también el balance, restando al resultado de la subconsulta que muestra el total de débitos el resultado de la subconsulta que muestra el total de créditos. Si las cuentas poseen uno o más débitos y créditos todo marcha de maravillas, pero el problema se presenta cuando una de las subconsultas (Débitos o Créditos) trae un valor null (una cuenta puede no tener débitos o créditos), en tal caso el resultado de la diferencia entre las subconsultas de débitos y créditos es igual a null, ya que null-valor = null y valor - null = null.

Las gracias anticipadas,

Víctor.-

[DEPROE]

hay dos manerass de solucionar el problema

una es que al crear tus tablas declares que los valores numericos no puedan ser nulos y les pongas un valor por omición de cero

(es lo que yo hago aun en sql server que tiene una función para sustituir los valores nulos por lo que se quiera y esta es isnull(calmpo, valor) si campo es nulo regresa el valor, no se si intebase la tenga)

la otra forma es mucho mas laborioza y es hacer la union de varias consultas (filtrando en cada consulta los registros que tengan nulos y cambiando las operaciones en cada caso)
ejemplo:
select nombre, cargos-abonos saldo from tabla
where cargo is not null and abono is not null
union
select nombre, cargos-abonos saldo from tabla
where cargo is not null and abono is null
union
select nombre, -abonos saldo from tabla
where cargo is null and abono is not null

(esto se pude poner muy complicado si son mas campos)

espero te sirva esto

[roman]

Empezado por DEPROE

hay dos manerass de solucionar el problema

una es que al crear tus tablas declares que los valores numericos no puedan ser nulos y les pongas un valor por omición de cero

Me parece que el problema no es que tenga valores null en las tablas sino que algunas cuentas no presentan movimientos de algún tipo (débito o crédito) por lo que al hacer el sum() el resultado es null. Por ejemplo, si tiene los datos

+---------+----------+-------+
| cod_cta | tipo_mov | monto |
+---------+----------+-------+
|    2    |     D    |   30  |
+---------+----------+-------+
|    3    |     D    |   40  |
+---------+----------+-------+
|    1    |     C    |   50  |
+---------+----------+-------+
|    2    |     C    |   10  |
+---------+----------+-------+
|    2    |     C    |   20  |
+---------+----------+-------+
|    3    |     D    |   10  |
+---------+----------+-------+
|    1    |     C    |   50  |
+---------+----------+-------+
|    1    |     D    |   20  |
+---------+----------+-------+

la subconsulta

Código SQL [-]
(select sum(monto) from movimientos where cod_cta = cuenta.codigo and tipo_mov='C')

devolverá null para CUENTA.CODIGO = 3.

----------------------

Estuve investigando un poco.

En MySql puede usarse la función if():

Código SQL [-]
select
    cod_cta,
    sum(if(tipo_mov = 'C', monto, 0)) as credito,
    sum(if(tipo_mov = 'D', monto, 0)) as debito,
    sum(if(tipo_mov = 'C', monto, -monto)) as balance
from
    movimientos
group by
    cod_cta

para regresar una tabla con crédito, débito y balance como requiere vpepen

Sin embargo no tenia ni idea de si Interbase disponía de una función similar. Lo más cercano que tenía a la mano era FireBird en donde logré mi primera consulta SQL en FireBird:

Código SQL [-]
select
    cod_cta,
    sum(case when tipo_mov = 'C' then monto else 0 end) as credito,
    sum(case when tipo_mov = 'D' then monto else 0 end) as debito,
    sum(case when tipo_mov = 'C' then monto else -monto end) as balance
from
    movimientos
group by
    cod_cta

que funciona igual que la de MySql.

Busqué en Internet a ver si Interbase tenía algo semejante pero me encontré enlaces que claramente decían que no existía el case aunque ignoro si en las últimas versiones ya existe.

Finalmente me metí con los procedimientos almacenados y obtuve mi primer procedimiento almacenado:

Código SQL [-]
create procedure balances
returns (
    codigo integer,
    credito integer,
    debito integer,
    balance integer)
as
begin
  for select codigo from cuentas
  into :codigo do
  begin
    select sum(monto) from movimientos
    where cod_cta = :codigo and tipo_mov = 'C'
    into :credito;

    if (:credito is null) then credito = 0;

    select sum(monto) from movimientos
    where cod_cta = :codigo and tipo_mov = 'D'
    into :debito;

    if (:debito is null) then debito = 0;

    balance = :credito - :debito;
    suspend;
  end
end

que genera la tabla (con los datos anteriores):

| codigo | credito | debito | balance |
+--------+---------+--------+---------+
|   1    |   100   |   20   |    80   |
+--------+---------+--------+---------+
|   2    |    30   |   30   |     0   |
+--------+---------+--------+---------+
|   3    |     0   |   50   |   -50   |
+--------+---------+--------+---------+

que me parece es lo que requiere vpepen.

De hecho el procedimiento generará registros (con ceros en lugar de null) incluso para cuentas que no tengan ningún movimiento (ni crédito ni débito)

El procedmiento podría usarse así:

Código SQL [-]
select cuentas.nombre,  balances.*
from cuentas, balances
where cuentas.codigo = balances.codigo

En fin, como son mis primeros pasos en FireBird y procedimientos almacenados no me sorprendería que hubiera formas mucho más elegantes y sencillas, pero mientras tanto quizá le sirva a vpepen y a mi me sirve de ejercicio .

Supongo que la sintaxis de los procedimientos almacenados no diferirá gran cosa para Interbase.

// Saludos

[vpepen]

Roman,

Tú planteamiento de mí problema es 100% lo que me está sucediendo para esa consulta. La verdad que te has explicado mejor que yo al abordar el problemita.

Al igual que tú, he buscado alguna función en interbase que me devuelva un valos cero por ejemplo, en caso de null, pero no la he encontrado aún.

Todavía no tengo experiencia con los procedimientos almacenados, pero entraré en ese campo, claro, tomando como base el ejemplo descrito por ti.

Ya postearé posteriormente como me va con los procedimientos almacenados.

Gracias a todos por su colaboración desinteresada,

Víctor.-

[frudolph]

La función que estás buscando se llama COALESCE y la documentación de la misma está en el archivo "Firebird_v15.108_ReleaseNotesSpanish.pdf" que lo podés bjar de la página "http://www.ibphenix.com".

Esta función te permite obtener valores no nulos: el uso sería más o menos el siguiente:

--> COALESCE(CampoDeLaTabla, 0)

para que te devuelva un cero en caso de que el Campo sea NULL.

[roman]

Empezado por frudolph

está en el archivo "Firebird_v15.108_ReleaseNotesSpanish.pdf"

El problema es que vpepen está usando Interbase y no Firebird. Y de hecho el mismo procedimiento almacenado que puse no estoy seguro de que funcione en Interbase. Quizá una UDF sería lo más conveniente.

// Saludos

[DEPROE]

trabajar tablas que permitan campos nulos es muy util, pero es necesaria mucha dedicación del programador para no tener problemas ya que cualquier operación que los implique dara por resultado un nulo

existen funciones para cada motor de base de datos que permiten manejar los nulos con seguridad como los ya nombrados

(por ejemplo: isnull, coalecse y case en sql serber)

y aun sin estas funciones se puede vivir con ellos

pero creanme cuando no sean necesarios los nulos no los usen,
declaren que los campos númericos no puedan ser nulos y con valor por default 0
y se quitan un monton de dolores de cabeza

yo soy un gran partidario de usar nulos, pero solo cuando es util su uso
y nunca configuro las bases de datos para que no use los nulos

saludos

[roman]

DEPROE

Yo concuerdo contigo en evitar el uso de valores null. Pero como ya te mencioné en mi respuesta a tu mensaje anterior, éste no es el problema aquí. Si te fijas en la tabla ejemplo que puse, no hay ningún valor null e incluso los campos podrían tener la restrición de no aceptar nulos. Los valores null surgen como consecuencia de la consulta en particular ya que puede haber cuentas sin un tipo de movimiento en particular.

vpepen

Además de todo lo dicho y los intentos realizados, lo cierto es que a veces conviene modificar un poco la estructura de nuestras tablas para adecuarse mejor al tipo de consultas que se hagan. En tu caso el campo Tipo_Mov indica si el monto es un débito o un crédito, que básicamente se traduce en anteponer o no un signo negativo al monto. Así como usas 'C' o 'D' bien podrías usar 'CREDITO' o 'DEBITO' o cualquier par de valores que disciernan entre un concepto y otro sin afectar la lógica del sistema.

Una vez asumido lo anterior podrías optar simplemente por los valores

 1 - crédito
-1 - débito

cambiando el tipo de datos de Tipo_Mov a un tipo numérico.

Con este simple cambio te evitas todos estos dolores de cabeza con esta consulta que te sirve incluso en Paradox:

Código SQL [-]
select
  cod_cta,
  sum((1 + tipo_mov)*monto/2) as credito,
  sum((1 - tipo_mov)*monto/2) as debito,
  sum(tipo_mov*monto) as balance
from
  movimientos
group by
  cod_cta

o bien, si quieres los nombres:

Código SQL [-]
select
  nombre,
  sum((1 + tipo_mov)*monto/2) as credito,
  sum((1 - tipo_mov)*monto/2) as debito,
  sum(tipo_mov*monto) as balance
from
  movimientos
left join
  cuentas on cuentas.codigo = movimientos.cod_cta
group by
  nombre

Lo único que estamos haciendo aquí es multiplicar monto por un factor adecuado. En el caso de crédito (1 + tipo_mov)/2 valúa a 1 si es un crédito o 0 si es un débito, de manera que sum() sólo suma los créditos. Análogamente, (1 - tipo_mov)/2 valúa a 0 si es un crédito o 1 si es un débito de manera que sum() sólo suma los débitos, y en el caso del balance, tipo_mov simplemente multiplica por -1 los débitos.

Así como usas 1 y -1 podrías usar cualquier par de valores numéricos. Los factores serían un poco más complejos pero aún así evitas tanta vuelta y dolor de cabeza.

// Saludos

[vpepen]

Roman,

Pondré en práctica las alternativas posteadas por ti y ya te informaré más adelante cómo me va. Te agradezco tu tiempo y el de los demás compañeros, asi como principalmente el conocimiento compartido.

Víctor.-

[Carmelo Cash]

Quería saber si alguien había descubierto cemo resolver el null en "InterBase".
el problema es que hago

Select A, B ,C ,A+B+C as Total
From prueba

y si alguno de los campos es null total es null.

Muchas gracias.

[Neftali [Germán.Estévez]]

Empezado por Carmelo Cash

... y si alguno de los campos es null total es null.

Normalmente esop se resuelve utilizando la funcion IF; La mayoría de SBGD's la tienen, supongo que interbase también.