Crosstab query using the DB2 stored procedure


     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

Who is Participating?
sachinwadhwaConnect With a Mentor Commented:
if you think logically ... its not a very hard !!

I can give you hint....

use SUM function
Kent OlsenData Warehouse Architect / DBACommented:
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!
KornipalliAuthor Commented:
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.

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
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!
use mine & kent's hint togeather and you will reach to answer quickly...
KornipalliAuthor Commented:
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

ocgstylesConnect With a Mentor Commented:
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.