Re: Help with SQL statement

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You have to remove it from the GROUP BY also.

-Charles

On Wed, Sep 9, 2009 at 8:33 AM, Smith, Mike<Mike_Smith@xxxxxxxxxxxxxxxx> wrote:
> Bruce, the only reason I had plbptp in my select is because I was trying
> to figure out why I am getting the multiple records.  I have removed it
> but I'm still getting the same result.
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of R Bruce Hoffman
> Sent: Wednesday, September 09, 2009 8:21 AM
> To: Midrange Systems Technical Discussion
> Subject: Re: Help with SQL statement
>
> If you don't want plbptp in your output, why is included in output and
> grouped? It doesn't have to be in the result set just because it's a
> predicate.
>
> Smith, Mike wrote:
> > I have the following SQL statement.
> > SELECT eeun, eedpt, plbemp, eenam, plbbg1, sum(plbhrs), plbptp FROM
> > PRLBH, EEMP
> > WHERE PLBEMP = EEENO   and PLBPTP in('CO','OH', '6D','7D','OG') and
> > EETDT = 0 and
> >  EEDPT   in (select PRDPT from PROVRDPT00 where PRNAME = 'BRANDTNS')
> and
> > PLBEDT
> > = 20090731
> >  GROUP BY PLBEMP,EENAM ,EEDPT, EEUN, PLBBG1 ,plbptp
> >    ORDER BY EEDPT,PLBEMP
> >
> > That produces results like this
> > Eedpt     plbemp      eenam
> > plbbg1       eeun      sum(plbhrs)             PLBPTP
> > 091         165     SMITH MICHAEL A                        091
> > Y      16.00         470.00      OH
> > 091         165     SMITH MICHAEL A                        091
> > Y       5.50          470.00      6D
> > 091         165     SMITH MICHAEL A                            091
> > Y         16.00         470.00      6C
> >
> > I get 3 records because there are three different values of PLBPTP
> >
> > I can't get my grouping to work so that I get 1 record that looks like
>
> > this
> >
> > 091   165     SMITH, MICHAEL A                        091     Y
> > 37.50      470.00
> >
> > Can you suggest a way to make this work?
> >
> > Thanks
> >
> > Mike
> >
> >
> >
> > NOTICE:  This message, including any attachment, is intended as a
> confidential and privileged communication. If you have received this
> message in error, or are not the named recipient(s), please immediately
> notify the sender and delete this message.
> >
>
> --
> "When I die, I want to die like my grandmother who died peacefully
>  in her sleep. Not screaming like all the passengers in her car."
>    - Author Unknown
>
> ===========================================================
> R Bruce Hoffman
>
>
> --
> 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.
>
>
>
>
> NOTICE:  This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
> --
> 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.