La expresión SQL CASE usando una tabla intermedia en Ms Access

Problema

Queremos replicar la expresión SQL CASE, actualmente no disponible, en Ms Access. En esta ocasión usaremos una tabla intermadia para crear los invervalos.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

En nuestro ejemplo para la columna Num queremos crear intervalos de 0 a >1000 con incrementos de 100. Usamos paréntesis y corchetes para denotar los intervalos semi-abiertos y semi-cerrados.

Solución

1. Creamos una tabla intermedia con los intervalos

2. Unimos ambas tablas basándonos en los límites inferior y superior de los intervalos

SELECT Tabla.Num, intervalos.Intervalos
FROM Tabla INNER JOIN intervalos ON (Tabla.Num > intervalos.inferior) AND (Tabla.Num <= intervalos.superior);

Entradas relacionadas

• La expresión SQL CASE en Ms Access

La expresión SQL CASE en Ms Access

Problema

Queremos replicar la expresión SQL CASE, actualmente no disponible, en Ms Access.

CASE
    WHEN condición1 THEN resultado1
    WHEN condición2 THEN resultado2
    WHEN condiciónN THEN resultadoN
    ELSE resultado
END;

En nuestro ejemplo para la columna Num queremos crear intervalos de 0 a >1000 con incrementos de 100. Usamos paréntesis y corchetes para denotar los intervalos semi-abiertos y semi-cerrados.

Solución

1. SiInm

SiInm:SiInm([Num]<=100, "(0,100]"
,SiInm([Num]<=200,"(100-200]",
SiInm([Num]<=300,"(200-300]",
SiInm([Num]<=400,"(300-400]",
SiInm([Num]<=500,"(400-500]",
SiInm([Num]<=600,"(500-600]",
SiInm([Num]<=700,"(600-700]",            
SiInm([Num]<=800,"(700-800]",              
SiInm([Num]<=900,"(800-900]",
SiInm([Num]<=1000,"(900-1000]",
SiInm([Num]>1000,">1000",""
)))))))))))

Usamos SiInm anidados para crear los intervalos. Necesitamos sere muy cuidadosos para incluir todos los paréntesis.
2. Conmutador

Conmutador:
Conmutador([Num]<=100,"(0,100]"
,[Num]<=200,"(100-200]"
,[Num]<=300,"(200-300]"
,[Num]<=400,"(300-400]"
,[Num]<=500,"(400-500]"
,[Num]<=600,"(500-600]"
,[Num]<=700,"(600-700]"
,[Num]<=800,"(700-800]"
,[Num]<=900,"(800-900]"
,[Num]<=1000,"(900-1000]"
,[Num]>1000,">1000")

Conmutador tiene una sintaxis más clara. Evitamos usar condiciones anidadas mediante pares de expresiones y valores.

Referencias

• SiInm
• Conmutador

Entradas relacionadas

• La expresión SQL CASE usando una tabla intermedia en Ms Access

Equivalente a coalesce en Excel

Problema

Queremos replicar la función coalesce en Excel. En nuestro ejemplo queremos que nos devuelva por fila la primera ocurrencia no en blanco.

Solución

1. INDICE y COINCIDIR con ESBLANCO.

{=INDICE(A2:F2,COINCIDIR(FALSO,ESBLANCO(A2:F2),FALSO))}

Al ser una fórmula matricial, presionamos Ctrl + Mayús + Entrar. Esta fórmula funcionará correctamente mientras no haya cadenas de texto de longitud cero (""). Por ejemplo, para la fila 5 de nuestro ejemplo. De lo contrario devolverá esa cadena de texto en lugar del primer número.
2. INDICE y COINCIDIR con IGUAL

{=INDICE(A2:F2,COINCIDIR(FALSO,IGUAL("",A2:F2),FALSO))}

Al ser una fórmula matricial, presionamos Ctrl + Mayús + Entrar. Esta fórmula resolverá el problema anterior con celdas que contengan cadenas de texto de longitud cero.

Resultados

SQL CASE Statement using an intermediate table in Ms Access

Problem

We need to create a SQL CASE Statement which is not currently supported in Ms Access. This time we will use an intermediate table to create the intervals.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In our example for the column Number we want to create intervals of 100 from 0 to >1000. We will use the interval notation: parentheses and/or brackets are used to show whether the endpoints are excluded or included respectively.

Solution

1. Create an intermediate table with the intervals

2. Join both tables based on the lower and upper bounds of the intervals.

SELECT Table.Number, intervals.Interval
FROM [Table] INNER JOIN intervals ON (Table.Number > intervals.lower) AND (Table.Number <= intervals.upper);

Related posts

• SQL CASE Statement in Ms Access

SQL CASE Statement in Ms Access

Problem

We need to create a SQL CASE Statement which is not currently supported in Ms Access

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In our example for the column Number we want to create intervals of 100 from 0 to >1000. We will use the interval notation: parentheses and/or brackets are used to show whether the endpoints are excluded or included respectively.

Solution

1. IIF

IFF:IIf([Number]<=100, "(0,100]"
,IIf( [Number]<=200,"(100-200]",
IIf([Number]<=300,"(200-300]",
IIf([Number]<=400,"(300-400]",
IIf([Number]<=500,"(400-500]",
IIf([Number]<=600,"(500-600]",
IIf([Number]<=700,"(600-700]",            
IIf([Number]<=800,"(700-800]",              
IIf([Number]<=900,"(800-900]",
IIf([Number]<=1000,"(900-1000]",
IIf([Number]>1000,">1000",""
)))))))))))

We use nested IIF statements to create the intervals. We need to be very careful to include all parentheses.
2. SWITCH

SWTICH:
Switch([Number]<=100,"(0,100]"
,[Number]<=200,"(100-200]"
,[Number]<=300,"(200-300]"
,[Number]<=400,"(300-400]"
,[Number]<=500,"(400-500]"
,[Number]<=600,"(500-600]"
,[Number]<=700,"(600-700]"
,[Number]<=800,"(700-800]"
,[Number]<=900,"(800-900]"
,[Number]<=1000,"(900-1000]"
,[Number]>1000,">1000")

SWITCH has a cleaner syntax. We avoid nesting all conditions using pairs of expressions and values.

References

• IIF
• SWITCH

Related posts

• SQL CASE Statement using an intermediate table in Ms Access

Coalesce cells in Excel

Problem

We'd like to coalesce cells in a row in Excel. In our example we'd like the first non-blank occurrence found for each row.

Solution

1. INDEX and MATCH in conjunction with ISBLANK

{=INDEX(A2:F2,MATCH(FALSE,ISBLANK(A2:F2),FALSE))}

We need to press CTRL + SHIFT + ENTER to enter this array formula. This formula works fine as long as there are not zero-length string characters ("") in the cells. E.g.: row 5 in our example. Otherwise it will return that zero-length string instead of the first number.
2. INDEX and MATCH in conjunction with EXACT

{=INDEX(A2:F2,MATCH(FALSE,EXACT("",A2:F2),FALSE))}

We need to press CTRL + SHIFT + ENTER to enter this array formula. This will solve the issue of cells containing zero-length string characters.

Results