Consulta UNION sin tabla en MS Access (Jet / ACE)

Esto funciona como se esperaba:

SELECT "Mike" AS FName 

Esto falla con el error "La input de consulta debe contener al less una tabla o consulta":

 SELECT "Mike" AS FName UNION ALL SELECT "John" AS FName 

¿Es solo una peculiaridad / limitación del motor de database Jet / ACE o me falta algo?

No pasaste por alto nada. El motor de database de Access permitirá seleccionar una sola fila sin un origen de datos FROM . Pero si desea UNION o UNION ALL las filas múltiples, debe include un FROM … incluso si no está haciendo reference a ningún campo de esa fuente de datos.

Creé una tabla con una fila y agregué una restricción de verificación para garantizar que siempre tendrá una y solo una fila.

 Public Sub CreateDualTable() Dim strSql As String strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);" Debug.Print strSql CurrentProject.Connection.Execute strSql strSql = "INSERT INTO Dual (id) VALUES (1);" Debug.Print strSql CurrentProject.Connection.Execute strSql strSql = "ALTER TABLE Dual" & vbNewLine & _ vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _ vbTab & "CHECK (" & vbNewLine & _ vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _ vbTab & vbTab & ");" Debug.Print strSql CurrentProject.Connection.Execute strSql End Sub 

Esa tabla Dual es útil para consultas como esta:

 SELECT "foo" AS my_text FROM Dual UNION ALL SELECT "bar" FROM Dual; 

Otro enfoque que he visto es utilizar una instrucción SELECT con TOP 1 o una cláusula WHERE que restringe el set de resultados a una sola fila.

Las restricciones de verificación de notas se agregaron con Jet 4 y solo están disponibles para las sentencias ejecutadas desde ADO. CurrentProject.Connection.Execute strSql funciona porque CurrentProject.Connection es un object ADO. Si intenta ejecutar la misma instrucción con DAO (es decir, CurrentDb.Execute o desde el diseñador de consultas de Access), obtendrá un error de syntax porque DAO no puede crear restricciones de verificación.

Si tiene acceso a algunas tablas del sistema, puede emular una tabla doble de esta manera:

 (SELECT COUNT(*) FROM MSysResources) AS DUAL 

Desafortunadamente, no conozco ninguna tabla de sistema que …

  • están siempre disponibles, legibles (es posible que MSysObjects no esté accesible para todas las conexiones)
  • contiene exactamente un logging, como DUAL de Oracle o SYSIBM.DUAL de DB2

Entonces escribirías:

 SELECT 'Mike' AS FName FROM (SELECT COUNT(*) FROM MSysResources) AS DUAL UNION ALL SELECT 'John' AS FName FROM (SELECT COUNT(*) FROM MSysResources) AS DUAL 

Esto es lo que se está implementando como un elemento sintáctico en jOOQ , por ejemplo.

Cuando tiene acceso restringido de solo lectura a la database (es decir, no puede crear nuevas tablas o acceder a los resources del sistema), esto podría funcionar:

 SELECT "Mike" AS FName FROM (SELECT COUNT(*) FROM anyTable WHERE 1=0) AS dual 
  1. anyTable es la primera tabla de usuarios que encuentras (¡No puedo imaginar una database de la vida real sin tabla de usuarios!).

  2. DONDE se supone que 1 = 0 devuelve rápidamente un recuento de 0, incluso en una tabla grande (con suerte, el motor Jet es lo suficientemente inteligente como para reconocer una condición tan trivial).

Si alguien quiere usar el método Top 1, se vería así:

 SELECT first_name AS FName FROM tblname UNION ALL SELECT "Mike" as Fname FROM (Select Top 1 Count(*) FROM tblsometable); 

El alias para el campo debe ser el mismo en ambos lados de la unión, en este caso "FName".

Aquí hay una forma mucho más simple de hacerlo:

 SELECT 'foo', 'boo', 'hoo' from TableWith1Row union SELECT 'foo1', 'boo1', 'hoo1' from TableWith1Row 

Importante: TableWith1Row puede ser una tabla con literalmente 1 logging (que usted ignora de todos modos) O puede ser una tabla con cualquier cantidad de filas (debe tener POR LO MENOS 1 fila) pero agrega una cláusula WHERE para asegurar 1 fila. Esto es un poco flojo, pero es una manera rápida de hacer que esto funcione sin crear más tablas.