Re: Do you see something wrong in my small change to this sql?

[Jeff Young <jyoung0950@xxxxxxxxx>]

The problem is that you want to return (or show) the item number, but you
are using a group by.
All fields in a select using group by MUST be either group by fields, or a
computed field.  using MAX or MIN is one way of fooling sql when there is
only one record for each value being returned.

Jeff Young
Sr. Programmer Analyst

On Fri, Mar 31, 2017 at 10:53 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

> This sql is currently working. If you look at the 7th line, what I want now
> it to use the idprt# which is their item number in a new version so the
> user could select per which item(s) they wish to see. but when i simply,
> take away the max it gives error when trying to run: I get error SQL 0122
> IDPRT# in select is not valid. why is this, I just want to have that field
> as a param.
>
> 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
>  FROM   ASTDTA"."OEIND94"
>               "OEIND94" INNER JOIN "ASTDTA"."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"
>  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.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD