SQL Server Performance Forum – Threads Archive
Problem with a difficult query
Hello: I have a total of 4 tables (PEDLINEA, PEDCABE, PLAN, PEDCOLINEA) with the following fields: PEDLINEA fechadocumento
linea
almacen
articulo
descripcion
cantidad
precio
PEDCABE documento
codigo_vendedor
PLAN codigo_vendedor
titulo
PEDCOLINEA articulo
cantidad (it’s a numeric field)
All the fields with the same name are foreig keys (in italic).
I tried to do a SQL query to show some fields and the sum of "cantidad". Something like this…
SELECT pedlinea.fecha FECHA,
pedlinea.documento DOCUMENTO,
plan.titulo TITULO,
pedlinea.linea LINEA,
pedlinea.almacen ALMACEN,
pedlinea.articulo ARTICULO,
pedlinea.descripcion DESCRIPCION,
pedlinea.cantidad CANTIDAD,
pedlinea.precio PRECIO,
pedcabe.codigo_vendedor CODIGO_VENDEDOR,
sum(pedcolinea.cantidad) FROM pedlinea, pedcabe, plan, pedcolinea
WHERE pedlinea.documento = pedcabe.documento
AND pedcabe.codigo_vendedor = plan.codigo_vendedor
and pedcolinea.articulo = pedlinea.articulo
However, it doesn’t work. Maybe I have to do a "group by" sentence (but with what fields?), I don’t know…. Do you know hoy to solve this problem? Thanks for your answer!!!!
Yes, of course you have to add a GROUP BY clause. It needs to include all the columns besides the one that you’re summing.
Here is the SQL sentence. First says "unrecoverable error" and then the connection with the SQL Server is closed. Why does it happen? Is the Query wrong?
SELECT pedlinea.fecha FECHA,
pedlinea.documento DOCUMENTO,
plan.titulo TITULO,
pedlinea.linea LINEA,
pedlinea.almacen ALMACEN,
pedlinea.articulo ARTICULO,
pedlinea.descripcion DESCRIPCION,
pedlinea.cantidad CANTIDAD,
pedlinea.precio PRECIO,
pedcabe.codigo_vendedor CODIGO_VENDEDOR,
sum(pedcolinea.cantidad) FROM pedlinea,pedcabe,plan, pedcolinea
WHERE pedlinea.documento = pedcabe.documento
AND pedcabe.codigo_vendedor = plan.codigo
and pedcolinea.articulo = pedlinea.articulo
group by pedlinea.fecha, pedlinea.documento, plan.titulo,
pedlinea.linea, pedlinea.almacen, pedlinea.articulo, pedlinea.descripcion,
pedlinea.cantidad, pedlinea.precio, pedcabe.codigo_vendedor
ORDER BY pedcabe.codigo_vendedor
In the column list, drop the aliases: pedlinea.fecha FECHA, >>> pedlinea.fecha,
It still doesn’t work….. [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]
What happens if you run a simple query, like SELECT pedlinea.*
FROM pedlinea,pedcabe,plan, pedcolinea
WHERE pedlinea.documento = pedcabe.documento
AND pedcabe.codigo_vendedor = plan.codigo
and pedcolinea.articulo = pedlinea.articulo ?
It runs perfectly
Then try this … SELECT pedlinea.documento
FROM pedlinea,pedcabe,plan, pedcolinea
WHERE pedlinea.documento = pedcabe.documento
AND pedcabe.codigo_vendedor = plan.codigo
AND pedcolinea.articulo = pedlinea.articulo
GROUP BY pedlinea.fecha, pedlinea.documento, plan.titulo,pedlinea.linea, pedlinea.almacen, pedlinea.articulo, pedlinea.descripcion,pedlinea.cantidad, pedlinea.precio, pedcabe.codigo_vendedor … and start adding the other columns one-by-onne, until you get the error again.
Wowww…. the row failing is sum(—-). I think the reason is that this field accepts NULL and "" values, so the sum is uncorrect. One possibility is to sum just the correct fields doing something like:
WHERE […] AND pedcocabe.cantidad is not NULL AND pedcocabe.cantidad<>"" […]
However, doing this some rows (those where this conditions fails) will not appear and I want all the rows. Is it possible? By the way… I cannot change the table structure. Thanks.
If the pedcolinea.cantidad column is not of a numeric data type, then you may run into all sorts of errors. For handling nulls, use sum(CASE WHEN pedcolinea.cantidad IS NULL THEN 0 ELSE pedcolinea.cantidad END) … or … SUM(ISNULL(pedcolinea.cantidad, 0))
Mmmmm it looks well but i cannot execute this because it says: Column (case) not found in the tables of the query or Column (isnull) not found in the tables of the query
[xx(]
Are you actually using Microsoft SQL Server?
]]>