SQL: La clausula GROUP BY falta a no es valida

[menende...@gmail.com]

Hola necesito ayuda:

Tengo 4 sucursales  y un stock unificado.

lo venia usando y funcionaba bien, surgió que en cada lugar tenga 2 depósitos y le agregue:

 IF( b.d20depo = "00001", SUM( b.d20cant ), 0) as "Dep01", IIF( b.d20depo = "00002", SUM( b.d20cant ), 0) as "Dep02"

 y me empezó a salir al ejecutar  -SQL: La clausula GROUP BY falta a no es valida-

la pregunta es puedo utilizar el IIF con GROUP BY ?

SELECT  a.d6prod, a.d6desc, SUM( b.d20cant ) as "Cant", IIF( b.d20depo = "00001", SUM( b.d20cant ), 0) as "Dep01", IIF( b.d20depo = "00002", SUM( b.d20cant ), 0) as "Dep02"    FROM _06 a;
  INNER JOIN _I20 b ON a.d6prod = b.d20prod;
  WHERE a.D6gru5 = "001" .and. a.D6acti = "S";
  GROUP BY a.d6prod, a.d6desc;
  UNION all;
  SELECT  a.d6prod, a.d6desc, SUM( b.d20cant ) as "Cant", IIF( b.d20depo = "00001", SUM( b.d20cant ), 0) as "Dep01", IIF( b.d20depo = "00002", SUM( b.d20cant ), 0) as "Dep02"    FROM _06 a;
  INNER JOIN _O20 b ON a.d6prod = b.d20prod;
  WHERE a.D6gru5 = "001" .and. a.D6acti = "S";
  GROUP BY a.d6prod, a.d6desc;
  UNION all;
  SELECT  a.d6prod, a.d6desc, SUM( b.d20cant ) as "Cant", IIF( b.d20depo = "00001", SUM( b.d20cant ), 0) as "Dep01", IIF( b.d20depo = "00002", SUM( b.d20cant ), 0) as "Dep02"    FROM _06 a;
  INNER JOIN _P20 b ON a.d6prod = b.d20prod;
  WHERE a.D6gru5 = "001" .and. a.D6acti = "S";
  GROUP BY a.d6prod, a.d6desc;
  UNION all;
  SELECT  a.d6prod, a.d6desc, SUM( b.d20cant ) as "Cant", IIF( b.d20depo = "00001", SUM( b.d20cant ), 0) as "Dep01", IIF( b.d20depo = "00002", SUM( b.d20cant ), 0) as "Dep02"    FROM _06 a;
  INNER JOIN _D20 b ON a.d6prod = b.d20prod;
  WHERE a.D6gru5 = "001" .and. a.D6acti = "S";
  GROUP BY a.d6prod, a.d6desc;
  order BY a.d6prod INTO CURSOR curTemp

Desde ya muchas gracias

[Zarlu]

Buenas tardes menendezjavier!

Prueba SET ENGINEBEHAVIOR 70 antes del Select

Suerte

zarlu

Chetumal, Quintana Roo, México

[Víctor Hugo Espínola Domínguez]

>IIF( b.d20depo = "00001", SUM( b.d20cant ), 0) as "Dep01"

SUM(IIF( b.d20depo = "00001",  b.d20cant, 0)) as "Dep01"

Saludos,

Víctor.

Lambaré - Paraguay.

--
Blog de la Comunidad Visual FoxPro en Español: http://comunidadvfp.blogspot.com
---
Has recibido este mensaje porque estás suscrito al grupo "Comunidad de Visual Foxpro en Español" de Grupos de Google.
Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a publicesvfoxp...@googlegroups.com.
Para ver esta conversación en el sitio web, visita https://groups.google.com/d/msgid/publicesvfoxpro/18d14b4e-ab5a-423a-8a4d-e186b097762cn%40googlegroups.com.

[menende...@gmail.com]

Gracias Victor así no me da el error