MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » August 2014

RE: WHERE clause issue



fixed

NUM_UNITS is an aggregate field. Those need to be checked in the HAVING
clause.

Try this:

SELECT "OEIND94"."IDDOCD" AS INV_DATE,
"OEIND94"."IDORD#" AS ORD_NUM,
"OEIND94"."IDORDT" AS ORD_TYPE,
"OEIND94"."IDPRLC" AS PROD_FAMILY,
"OEIND94"."IDPR$C" AS PRICE_CODE,
"OEIND94"."IDCOM#",
MAX("OEIND94"."IDPRT#") AS ITEM_REF,
"ICPRT1"."IARCC4" AS PROD_TYPE,
"ICPRT1"."IARC11" AS PROD_CLASS,
"ICPRT1"."IACMQC" AS CUM_CODE,
SUM("OEIND94"."IDSHP#") AS NUM_UNITS,
SUM("OEIND94"."IDSHP#" * "OEIND94"."IDCSU$") AS TOT_COST,
SUM("OEIND94"."IDSHP#" * "OEIND94"."IDNTU$") AS TOT_SLS
,"ICPRT1"."IA101"
FROM "M400"."DTA"."OEIND94"
"OEIND94" INNER JOIN "M400"."DTA"."ICPRT1"
"ICPRT1" ON "OEIND94"."IDPRT#"="ICPRT1"."IAPRT#"
WHERE "OEIND94"."IDCOM#"='001' AND
"OEIND94"."IDDOCD" >= {?FromDate} AND
"OEIND94"."IDDOCD" <= {?ToDate} AND
"ICPRT1"."IARCC4"='FIN'
GROUP BY "OEIND94"."IDDOCD",
"OEIND94"."IDORD#",
"OEIND94"."IDORDT",
"OEIND94"."IDPRLC",
"OEIND94"."IDPR$C",
"OEIND94"."IDCOM#",
"ICPRT1"."IARCC4" ,
"ICPRT1"."IARC11",
"ICPRT1"."IACMQC",
"ICPRT1"."IA101"
HAVING NUM_UNITS = 1
ORDER BY "OEIND94"."IDDOCD",
"OEIND94"."IDORD#",
"OEIND94"."IDORDT",
"OEIND94"."IDPRLC",
"OEIND94"."IDCOM#"


If that gives you an error, you may need to use:

HAVING SUM("OEIND94"."IDSHP#") = 1


Have fun!
Richard


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Hoteltravelfundotcom
Sent: Monday, August 11, 2014 6:40 PM
To: Midrange Systems Technical Discussion
Subject: WHERE clause issue

HI I Need to change this sql so that it will select only those orders that
have a total of 1 Qty to the order. this we can get from NUM_UNITS.

i tried adding where BUT it is not accepting.. I added the NUM_UNITS = 1 in
the WHERE CLAUSE but i get an error. 'COLUMN OR GLOBAL VARIABLE not FOUND;
i also tried the SUM("OEIND94"."IDSHP#") =1 but this does not work as well.


SELECT "OEIND94"."IDDOCD" AS INV_DATE,
"OEIND94"."IDORD#" AS ORD_NUM,
"OEIND94"."IDORDT" AS ORD_TYPE,
"OEIND94"."IDPRLC" AS PROD_FAMILY,
"OEIND94"."IDPR$C" AS PRICE_CODE,
"OEIND94"."IDCOM#",
MAX("OEIND94"."IDPRT#") AS ITEM_REF,
"ICPRT1"."IARCC4" AS PROD_TYPE,
"ICPRT1"."IARC11" AS PROD_CLASS,
"ICPRT1"."IACMQC" AS CUM_CODE,
SUM("OEIND94"."IDSHP#") AS NUM_UNITS,
SUM("OEIND94"."IDSHP#" * "OEIND94"."IDCSU$") AS TOT_COST,
SUM("OEIND94"."IDSHP#" * "OEIND94"."IDNTU$") AS TOT_SLS
,"ICPRT1"."IA101"
FROM "M400"."DTA"."OEIND94"
"OEIND94" INNER JOIN "M400"."DTA"."ICPRT1"
"ICPRT1" ON "OEIND94"."IDPRT#"="ICPRT1"."IAPRT#"
WHERE "OEIND94"."IDCOM#"='001' AND
"OEIND94"."IDDOCD" >= {?FromDate} AND
"OEIND94"."IDDOCD" <= {?ToDate} AND
"ICPRT1"."IARCC4"='FIN' AND
NUM_UNITS = 1
GROUP BY "OEIND94"."IDDOCD",
"OEIND94"."IDORD#",
"OEIND94"."IDORDT",
"OEIND94"."IDPRLC",
"OEIND94"."IDPR$C",
"OEIND94"."IDCOM#",
"ICPRT1"."IARCC4" ,
"ICPRT1"."IARC11",
"ICPRT1"."IACMQC",
"ICPRT1"."IA101"
ORDER BY "OEIND94"."IDDOCD",
"OEIND94"."IDORD#",
"OEIND94"."IDORDT",
"OEIND94"."IDPRLC",
"OEIND94"."IDCOM#"
--







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact