ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Select Distinct Records

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Select Distinct Records

    Hi,

    In my file I have some records which are having the same values except one value.

    Following is the example

    Name Age Salary

    Muthu 40 10000
    Jamie 35 25000
    Rich 45 15000
    Julie 32 10000
    Muthu 40 5000
    Julie 32 2500
    Jamie 35 1000
    Muthu 40 2000

    from the above records, the fields NAME and AGE are having same values for the records while Salary is having different values.

    I want to sum up the Salary by selecting the Distinct records. I need the out put as follows

    Muthu 40 17000
    Jamie 35 26000
    Rich 45 15000
    Julie 32 12500

    I need to use SQL statements for this in my SQLRPGLE program.

    Let me know how to write the query..

    Thanks for the assistance. It is bit urgent..

    Muthu
    Miles to Go, Before I sleep.

  • #2
    Re: Select Distinct Records

    Code:
    Exec SQL Declare C1 cursor for
       Select Name, Age, Sum(Salary) from myfile
       group by Name, Age;
    Exec Sql Open C1;
    Dou SQLSTT <> '0000';
       Exec Sql Fecth C1 into :MYDS;
       IF SQLSTT = '00000';
    ....
    Patrick

    Comment


    • #3
      Re: Select Distinct Records

      Originally posted by K2r400 View Post
      Exec Sql Fecth C1 into :MYDS;
      Your codeth hath a lispeth.
      Michael Catalani
      IS Director, eCommerce & Web Development
      Acceptance Insurance Corporation
      www.AcceptanceInsurance.com
      www.ProvatoSys.com

      Comment


      • #4
        Re: Select Distinct Records

        now thats funny!
        All my answers were extracted from the "Big Dummy's Guide to the As400"
        and I take no responsibility for any of them.

        www.code400.com

        Comment


        • #5
          Re: Select Distinct Records

          Just read it today ... I snorted my Monster Drink out my nose...

          Thanks Michael!

          Comment

          Working...
          X