Union with different quantities of columns in the select-list?
I have this queries, but I need to make a Union for all. But each query has a different quantity of columns in the select-list, which gives me the error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have the same number of expressions in their target lists.
How can I fix this to avoid the error?
Select 'Pregunta:(8-9-10)Totales No Coinciden' 开发者_StackOverflow中文版as Descripcion_Error, c_Fk_IdBoleta as Boleta,
f_TotalAreaExtensionFinca as Extension_Total, f_TotalAreaDedicadaFinca
as Area_Dedicada_Finca, f_TotalAreaTenenciaFinca as Tenencia_Finca
from Fnc_TenenciaUsoTierra
where (f_TotalAreaExtensionFinca <> f_TotalAreaDedicadaFinca OR f_TotalAreaExtensionFinca <> f_TotalAreaTenenciaFinca)
AND Fnc_TenenciaUsoTierra.c_Fk_IdBoleta = @id_Boleta
UNION
SELECT 'Pregunta (12) El área sembrada es mayor al área dedicada a cultivos' as Descripcion_Error,
c_Fk_IdBoleta as Boleta,
(SELECT SUM(f_AreaDedicadaCultivos)
FROM Fnc_TenenciaUsoTierra
WHERE c_Fk_IdBoleta = sembrado.c_Fk_IdBoleta)
AS AreaDedicadaCultivos,
SUM(sembrado.f_AreaSiembra) as AreaSembrada
FROM
Clt_Sembrado as sembrado
WHERE
sembrado.c_Fk_IdBoleta = 45550711
GROUP BY sembrado.c_Fk_IdBoleta
HAVING SUM(sembrado.f_AreaSiembra) > (SELECT SUM(f_AreaDedicadaCultivos)
FROM Fnc_TenenciaUsoTierra
WHERE c_Fk_IdBoleta = sembrado.c_Fk_IdBoleta)
The problem is the number of columns in each part of the query, not the number of results. You have 5 columns in the top part
- Descripcion_Error,
- Boleta
- Extension_Total,
- Area_Dedicada_Finca,
- Tenencia_Finca
and 4 in the bottom.
- Descripcion_Error,
- Boleta,
- AreaDedicadaCultivos,
- AreaSembrada
To bring these back in one result set there must be the same number of the columns and the columns in the same ordinal positions need to have compatible datatypes.
It is not clear what your desired results should look like. If there is no suitable column to add in the second query then you could add a constant expression in place of one of the columns. In that case you would probably want UNION ALL
rather than UNION
(the difference between the two being that UNION
adds an additional duplicate removal step) or perhaps they should just be brought back as two separate results to your application.
You have to have the same number of columns on each side of the UNION. You can always add dummy columns to get there - and fill them with NULLs or the "not applicable" value of your choice.
SELECT 'Pregunta (12) El área sembrada es mayor al área dedicada a cultivos' as Descripcion_Error,
c_Fk_IdBoleta as Boleta,
(SELECT SUM(f_AreaDedicadaCultivos)
FROM Fnc_TenenciaUsoTierra
WHERE c_Fk_IdBoleta = sembrado.c_Fk_IdBoleta)
AS AreaDedicadaCultivos,
SUM(sembrado.f_AreaSiembra) as AreaSembrada,
NULL as Tenencia_Finca
FROM Clt_Sembrado as sembrado
精彩评论