|
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#"
--
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
