ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Add Group to group by

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

  • Add Group to group by

    Hi all,

    I've to create an xml, I made all via sql, but when I tried to validate my output Xml, this failed, because I didn't have all the group of tag requested by the schema.

    To resolve this I was thinking to add group to my table when I made the group by.

    If someone have a better idea please suggest it to me.

    Here's an example an what I want:

    Imaging that my schema want 9 kind of job for example:

    Code:
    <employee>
    <job>ANALYST</job>
    <numberofemployee>6</numberofemployee>
    <job>DESIGNER</job>
    <numberofemployee>20</numberofemployee>
    <job>CLERK</job>
    <numberofemployee>16</numberofemployee>
    <job>OPERATOR</job>
    <numberofemployee>12</numberofemployee>
    <job>PRES</job>
    <numberofemployee>2</numberofemployee>
    <job>MANAGER</job>
    <numberofemployee>14</numberofemployee>
    <job>SALESREP</job>
    <numberofemployee>4</numberofemployee>
    <job>ADMIN</job>
    <numberofemployee>0</numberofemployee>
    <job>FIELDREP</job>
    <numberofemployee>10</numberofemployee>
    </employee>
    but generating it from the table employee I din't find the ADMIN category:

    Code:
    with job(jobxml) as (
    Select Xmlconcat(XmlElement(Name "job", trim(Job)), XmlElement(Name "numberofemployee", count(*)))
        From employee
        group by job)
     Select xmlelement(Name "employee", xmlagg(jobxml))
     from job;
    
    <employee>
    <job>MANAGER</job>
    <numberofemployee>7</numberofemployee>
    <job>DESIGNER</job>
    <numberofemployee>10</numberofemployee>
    <job>OPERATOR</job>
    <numberofemployee>6</numberofemployee>
    <job>FIELDREP</job>
    <numberofemployee>5</numberofemployee>
    <job>PRES</job>
    <numberofemployee>1</numberofemployee>
    <job>ANALYST</job>
    <numberofemployee>3</numberofemployee>
    <job>CLERK</job>
    <numberofemployee>8</numberofemployee>
    <job>SALESREP</job>
    <numberofemployee>2</numberofemployee>
    </employee>

    So I think to make a model table with all the category requested by the schema and than a union with my employee table, a better complicated because I had to make it a lot of times...

    but this is the idea:

    Code:
    Declare global temporary table modello as 
    (
    Select Job,
           Count(*) as conta
      From Sample/Employee
      Group By Job ) with data;
    select *From modello;
    
    insert into modello values('ADMIN', 0);
    
    With job1 (job, conta) As (
        Select job, sum(conta)
          From Modello
          group by job
        Union All
          Select Job,
                 Count(*) As Conta
            From Sample/Employee
            Group By Job 
      ),
    job2 (jobxml) as (
      Select Xmlconcat(XmlElement(Name "job", trim(Job)), XmlElement(Name "numberofemployee", Sum(conta)))
        From job1
        group by job)
     Select xmlelement(Name "employee", xmlagg(jobxml))
     from job2
    ;
    Does anyone have a better idea?

    Many thanks.

    Bye


  • #2
    I'd do it in this way (I tried to correct my previous example, but was not allowed to do it)
    I'do it in this way:

    Code:
     With b as (Select Coalesce(a.Job, x.Job) Job, Count(Conta) Conta
                 from a full join (Values('MANAGER'), ('DESIGNER'), ('OPERATOR'), ('FIELDREP'),
                                         ('PRES'),    ('ANALYST'),   ('CLERK'),   ('SALESREP'), ('ADMIN')) x (Job)
                      on a.Job = x.Job
                 Group By Coalesce(a.Job, x.Job))      
    Select XMLElement(Name "employee",
              XMLAgg(XMLConcat(XMLElement(Name"job", job),
                               XMLElement(Name "numberofemployee", Conta))
                     Order By Job))
    from b;

    Comment


    • #3
      Originally posted by B.Hauser View Post
      I'd do it in this way (I tried to correct my previous example, but was not allowed to do it)
      I'do it in this way:

      Code:
       With b as (Select Coalesce(a.Job, x.Job) Job, Count(Conta) Conta
      from a full join (Values('MANAGER'), ('DESIGNER'), ('OPERATOR'), ('FIELDREP'),
      ('PRES'), ('ANALYST'), ('CLERK'), ('SALESREP'), ('ADMIN')) x (Job)
      on a.Job = x.Job
      Group By Coalesce(a.Job, x.Job))
      Select XMLElement(Name "employee",
      XMLAgg(XMLConcat(XMLElement(Name"job", job),
      XMLElement(Name "numberofemployee", Conta))
      Order By Job))
      from b;
      Many thanks Birgitta, It's perfect!!!

      Bye

      Comment


      • #4
        Hi all,

        the solution proposed by Birgitta is perferct, but I still have a question, imagine this sql:

        Code:
        Select x.job, a.sex, count(a.job) 
            From employee a
            full join 
            (Values('ANALYST') , ('DESIGNER') , ('CLERK'), ('OPERATOR'), ('PRES'), ('MANAGER'), ('SALESREP'), ('FIELDREP'), ('ADMIN'))  x (job)
            on a.job = x.job and a.sex = 'M'
            group by x.job, a.sex;
        From my select I need to exclude all the Male for example, but in I put the conditions into the where clause I exclude the job 'ADMIN' because the admin job isn't in employee table, then if I put the condition in the join clause it doesn't take effect. Why? Anyone have a solutions?

        Many thanks.
        Bye

        Comment


        • #5
          Originally posted by paolinosal View Post
          Hi all,

          the solution proposed by Birgitta is perferct, but I still have a question, imagine this sql:

          Code:
          Select x.job, a.sex, count(a.job)
          From employee a
          full join
          (Values('ANALYST') , ('DESIGNER') , ('CLERK'), ('OPERATOR'), ('PRES'), ('MANAGER'), ('SALESREP'), ('FIELDREP'), ('ADMIN')) x (job)
          on a.job = x.job and a.sex = 'M'
          group by x.job, a.sex;
          From my select I need to exclude all the Male for example, but in I put the conditions into the where clause I exclude the job 'ADMIN' because the admin job isn't in employee table, then if I put the condition in the join clause it doesn't take effect. Why? Anyone have a solutions?

          Many thanks.
          Bye
          Trying with a right join it works.
          But I still don't understand why from the full join I can't exclude some records from the first table.

          Bye

          Comment

          Working...
          X