Mostrar un campo de otra consulta

flaviovich · #1 (**permalink**) 07/12/2006, 08:05

Bueno, vayamos al punto.
Tengo la sgte. sentencia:

Código:

SELECT ol.MaeCod, SUM(ol.OrdCant) as OrdCant, SUM(ol.OrdCaEnt) as OrdCaEnt
FROM RORDCAB oc, RORDLIN ol, RMAEBOD b
WHERE oc.CiaCod = ol.CiaCod
AND oc.OrdId = ol.OrdId
AND oc.OrdSec = ol.OrdSec
AND ol.CiaCod = b.CiaCod
AND ol.MaeCod = b.MaeCod
AND oc.OrdEst = 'A'
AND oc.OrdSF = 'N'
AND b.BdgId = 1
GROUP BY ol.MaeCod
ORDER BY ol.MaeCod
//go

SELECT oc.OrdId, ol.MaeCod, ol.OrdCant, ol.OrdCaEnt
FROM RORDCAB oc, RORDLIN ol, RMAEBOD b
WHERE oc.CiaCod = ol.CiaCod
AND oc.OrdId = ol.OrdId
AND oc.OrdSec = ol.OrdSec
AND ol.CiaCod = b.CiaCod
AND ol.MaeCod = b.MaeCod
AND oc.OrdEst = 'A'
AND oc.OrdSF = 'N'
AND b.BdgId = 1
//go

SELECT * FROM RMAEBOD
WHERE MaeCod = '7174165J0038B'
AND BdgId = 1

Esto muestra:

En la consulta de arriba, debe aparecerme el 4 si la resta de OrdCant - OrdCaEnt es diferente de MaePed.

Espero que se entienda.

Cualquier ayuda, consejo, etc. es bienvenido.
Gracias!

Gabo77 · #2 (**permalink**) 07/12/2006, 09:40

prueba con esto y me cuantas!

Código:

SELECT
	ol.MaeCod, SUM(ol.OrdCant) as OrdCant, SUM(ol.OrdCaEnt) as OrdCaEnt,
	CASE
		WHEN SUM(OL.ORDCANT) - SUM(OL.ORDENT) <> B.MAEPED THEN
			B.MAEPED
		ELSE
			/*VALOR POR DEFAULT*/
			NULL
	END AS RESULTADO
FROM
	RORDLIN OL
INNER JOIN
	RMAEBOD B
	ON
	B.CIACOD = OL.CIACOD
	AND
	B.MAECOD = OL.MAECOD
WHERE
	EXISTS(
		SELECT
			1
		FROM
			RORDCAB OC
		WHERE
			OC.ORDEST = 'A'
			AND
			OC.ORDFS = 'N'
			AND
			OC.CIACOD = OL.CIACOD
			AND
			OC.ORDID = OL.ORDID
			AND
			OC.ORDSEC = OL.ORDSEC
	)
	AND
	B.BDGID = 1

Saludos!

flaviovich · #3 (**permalink**) 07/12/2006, 11:50

Ya funciona, solo faltaba al final:
GROUP BY OL.MAECOD, B.MAEPED
ORDER BY OL.MAECOD
Solo hay un pero: solo necesito que salgan los registros que cumplen con la condicion en el WHEN, porque salen muchos (null).
¿Qué se debe hacer?

Gabo77 · #4 (**permalink**) 07/12/2006, 13:28

para el ejemplo que planteaste si dio el resultado?

Saludos!

Gabo77 · #5 (**permalink**) 07/12/2006, 13:43

aahh ya entendí tu pregunta, solo agregale un where con algo como esto

Código:

WHERE
        SUM(OL.ORDCANT) - SUM(OL.ORDENT) <> B.MAEPED

si entiendes que para esto, el CASE está de más no?

Saludos!

flaviovich · #6 (**permalink**) 07/12/2006, 16:14

Bueno, todo lo que necesitaba ya lo realize. Muchas gracias!
Solo no entendi donde poner ese WHERE.

Gabo77 · #7 (**permalink**) 07/12/2006, 16:31

Esta sería tu consulta final

Saludos!

Código:

SELECT
	ol.MaeCod, SUM(ol.OrdCant) as OrdCant, SUM(ol.OrdCaEnt) as OrdCaEnt, B.MAEPED AS RESULTADO
FROM
	RORDLIN OL
INNER JOIN
	RMAEBOD B
	ON
	B.CIACOD = OL.CIACOD
	AND
	B.MAECOD = OL.MAECOD
WHERE
	EXISTS(
		SELECT
			1
		FROM
			RORDCAB OC
		WHERE
			OC.ORDEST = 'A'
			AND
			OC.ORDFS = 'N'
			AND
			OC.CIACOD = OL.CIACOD
			AND
			OC.ORDID = OL.ORDID
			AND
			OC.ORDSEC = OL.ORDSEC
	)
	AND
	B.BDGID = 1
WHERE
        SUM(OL.ORDCANT) - SUM(OL.ORDENT) <> B.MAEPED
GROUP BY
        OL.MAECOD, B.MAEPED
ORDER BY
        OL.MAECOD

flaviovich · #8 (**permalink**) 07/12/2006, 17:24

Sale error: "Incorrect syntax near the keyword 'WHERE'."

Gabo77 · #9 (**permalink**) 08/12/2006, 09:33

Nobody's perfect!

Código:

SELECT
	ol.MaeCod, SUM(ol.OrdCant) as OrdCant, SUM(ol.OrdCaEnt) as OrdCaEnt, B.MAEPED AS RESULTADO
FROM
	RORDLIN OL
INNER JOIN
	RMAEBOD B
	ON
	B.CIACOD = OL.CIACOD
	AND
	B.MAECOD = OL.MAECOD
WHERE
	EXISTS(
		SELECT
			1
		FROM
			RORDCAB OC
		WHERE
			OC.ORDEST = 'A'
			AND
			OC.ORDFS = 'N'
			AND
			OC.CIACOD = OL.CIACOD
			AND
			OC.ORDID = OL.ORDID
			AND
			OC.ORDSEC = OL.ORDSEC
	)
	AND
	B.BDGID = 1
        AND
        SUM(OL.ORDCANT) - SUM(OL.ORDENT) <> B.MAEPED
GROUP BY
        OL.MAECOD, B.MAEPED
ORDER BY
        OL.MAECOD

flaviovich · #10 (**permalink**) 11/12/2006, 08:23

Ahora sale: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Gabo77 · #11 (**permalink**) 11/12/2006, 14:49

Código:

SELECT
	ol.MaeCod, SUM(ol.OrdCant) as OrdCant, SUM(ol.OrdCaEnt) as OrdCaEnt, B.MAEPED AS RESULTADO
FROM
	RORDLIN OL
INNER JOIN
	RMAEBOD B
	ON
	B.CIACOD = OL.CIACOD
	AND
	B.MAECOD = OL.MAECOD
WHERE
	EXISTS(
		SELECT
			1
		FROM
			RORDCAB OC
		WHERE
			OC.ORDEST = 'A'
			AND
			OC.ORDFS = 'N'
			AND
			OC.CIACOD = OL.CIACOD
			AND
			OC.ORDID = OL.ORDID
			AND
			OC.ORDSEC = OL.ORDSEC
	)
	AND
	B.BDGID = 1
GROUP BY
        OL.MAECOD, B.MAEPED
HAVING
        SUM(OL.ORDCANT) - SUM(OL.ORDENT) <> B.MAEPED
ORDER BY
        OL.MAECOD

Me rindo, es lo malo de no compilar! :( esta no debe fallar

flaviovich · #12 (**permalink**) 11/12/2006, 15:41

Ejecuta pero no muestra nada. Bueno, dejemoslo ahi, ya me has ayudado bastante, ademas que lo principal ya lo hice. Muchisimas gracias amigon!

Gabo77 · #13 (**permalink**) 11/12/2006, 16:50

Saludos!