MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » August 2014

Re: WHERE clause issue



fixed

After GROUP BY:

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



Regards
Carel Teijgeler

PS: Read some books or manuals on SQL

On 12-8-2014 0:40, Hoteltravelfundotcom wrote:
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