Crosstab query using the DB2 stored procedure

Posted on 2006-05-09
Last Modified: 2008-02-01

     I have a table called Problems with the following columns & data.

     Table Name: Problems

       Tkt#                       Problem_Code                Resolver_Group         Reporter_Group


    22                              Closed                           ABC                           Mechanical
    33                              Closed FC                      ABC                           IT
    11                              Resolved                        XYB                           Mechanical
    04                              Open                                                             IT
    99                              Closed FC                       XYB                          HR
    14                              Resolved                        PYT                           FI
    09                              Closed                           XYB                          HR


I want to produce the Cross tab report with the following format :

Resolver_Group            Closed            ClosedFc      Total

ABC                                 1                   1               2
XYB                                 2                   1               3
PYT                                 1                   0               1


Criteria for producing the above ourptut:

Exlude Open Ticktes
CLOSED = Closed & Resolved Tickets
CLOSEDFC - Closed-FC ticketes
Total = Total number os Closed & Closed Fc tickets.

Please let me know the solution as soon as possible.

Thank You

Question by:Kornipalli
    LVL 45

    Expert Comment

    Hi Kornipalli,

    This is clearly a homework assignment and the rules disallow us providing homework.  Make a stab at it and we'll be glad to help you.

    Good Luck!
    LVL 7

    Accepted Solution

    if you think logically ... its not a very hard !!

    I can give you hint....

    use SUM function

    Author Comment

    Hi Kent,
        I am not undersatdning what you meant by the homework assignment & the meaning of a stab.
        I have tried to do it by Cube function, but could not able to do it.
        Please let me know.

    LVL 45

    Assisted Solution

    Hi Kornipalli,

    Sorry.  I sometimes forget that American slang isn't universal.  In this case "stab" means attempt.  And the way the question is phrased leads me to believe that this is schoolwork.  The rules of the board prohibit us from giving complete answers to homework assignments, though we can help you to get to the answer.

    There are several ways to get there.  I suggest that the "more basic" method is to simply count the objects and put them into the report.  This is wordier than other solutions, but let's walk before we run.

    From the data in the table we only really care about two columns -- the problem_code and the resolver_group.  And regarding the problem_code, we only care about two of the possible status codes.

    So we simply count the rows with the correct status and join the result to the resolver group.

      (count (*) from Problems where Problem_code = 'Closed') as a,
      (count (*) from Problems where Problem_code = 'Closed FC') as b
    from Problems p
    where p.resolver_group = a.resolver_group
      and p.resolver_group = b.resolver_group;

    The SQL above will get you close, but it isn't a final answer.  Because I do believe this to be homework, I'll let you try and complete it.  If you're struggling, write back and I'll help you along.

    Good Luck!
    LVL 7

    Expert Comment

    use mine & kent's hint togeather and you will reach to answer quickly...

    Author Comment

    Hi Kent,
               Thank You for the response. It is not a Home assignment. I am a Fulltime employee & working on a project with the new tchnology IBM DB2.
               I have provided you the details just with general details.
               So, please let me know your soultion & it will be really a great help.
               I am also writing some code to test it out.

    Thank You

    LVL 5

    Assisted Solution

    the simplest i can think of would be to use a a combination of a SUM and CASE to get the answer:

    sum(case when problem_code in ('Closed', 'Resolved') then 1 else 0 end) as closed

    that should be able to help you along...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now