Convert Crosstab access query to DB2 sql query.

My  boss has been exporting several tables from our DB2 database into access and running an access wizard to create a Crosstab pivoted report. The access code is this.

TRANSFORM Sum(MM410ITL_INVBAL.IBHAND) AS SumOfIBHAND
SELECT MM410ITL_INVBAL.ISTORE
FROM MM410ITL_INVBAL INNER JOIN MM410ITL_INVMSTL1 ON MM410ITL_INVBAL.INUMBR = MM410ITL_INVMSTL1.INUMBR
GROUP BY MM410ITL_INVMSTL1.ISTYLN, MM410ITL_INVMSTL1.ISCOLR, MM410ITL_INVBAL.ISTORE
PIVOT MM410ITL_INVMSTL1.ISSIZE;

He has asked me to do a direct pull from the DB2 and create the same report. The problem is that the PIVOT keyword is not a valid statement and the above sql breaks. Can anyone help me convert this into a sql command that i can fire at the database and return a record set that will work?

Thanks for any help.


Smitty200Asked:
Who is Participating?
 
LowfatspreadCommented:
which version and Operating system of DB2 are you trying to access ?



the basic syntax for a pivot select is

Select Key
      , Sum(case when value = 1 then Col1 else null end) as Val1
      ,Sum(case when value = 2 then col1 else null end) As Val2
  ...
from ..
group by Key

but this assumes that you know the range of Values in advance...
if you do its relatively simple...

something along the lines of ...

SELECT IM.ISTYLN,IM.ISCOLR
           ,Sum(case when IB.ISTORE = '123' then IB.IBHAND else null end) as Store_123
           ,Sum(case when IB.ISTORE = '124' then IB.IBHAND else null end) as Store_124
           ,Sum(case when IB.ISTORE = '125' then IB.IBHAND else null end) as Store_125
           ,Sum(case when IB.ISTORE = '126' then IB.IBHAND else null end) as Store_126
  FROM MM410ITL_INVBAL as  IB
 INNER JOIN MM410ITL_INVMSTL1 as IM
     ON IB.INUMBR = IM.INUMBR
GROUP BY IM.ISTYLN
               , IM.ISCOLR


 
0
 
ghp7000Commented:
you cannot do it by using the same code as that generated by Access.
Try doing a group by rollup(col1, col2, col3 etc) and see if that gets you closer to what you want
0
 
Smitty200Author Commented:
Sorry my skills with SQL are limited to fairly low level types of queries. I am unfamiliar with rollup or how to use it.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ghp7000Commented:
cross tab queries in Access are fairly complicated, in db2 they are very complicated, therefore I think the best thing you can do is get some higher level assistance as it requires a very good knowledge of the data architecture, you can t simply port from access to db2.
0
 
LowfatspreadCommented:
>direct pull from DB2

... just make sure that you're DBA's are happy that a direct pull will be made and that it can
    be accomodated within there schedules...

     
0
 
Smitty200Author Commented:
SIGH sorry for the confusion. The boss has now given me the query for the data he wants in the report.

SELECT c.strnam, c.stphon, b.istyln, d.colsht, b.issize, e.sizseq,
sum(a.ibhand)
FROM invbal a, invmst b, tblstr c, tblcol d, tblsiz e
WHERE b.istyln = '152107' and b.iscolr = '1'
and a.inumbr = b.inumbr and a.istore = C.STRNUM
and d.colcod = b.iscolr
and b.issize = e.sizcod
group by c.strnam, c.stphon, b.istyln, d.colsht, b.issize, e.sizseq
order by c.strnam, c.stphon, b.istyln, d.colsht, b.issize, e.sizseq

When run against the tables it returns columns as below

Store#  Phone# Style# Color Size OnHand
  3           123      55      BL     sm    5
  3           123      55      BL     med  3
  3           123      55      BL     lrg     6

etc

What i would like to return is the same result except with the sm, med, lrg as there own columns so there would be 1 row for store 3 with the sizes as columns.
0
 
sachinwadhwaCommented:
Here is one more solution:

1) Install db2 client on access machine
2) configure db2 client to connect to database (including odbc)
3) create linked tables in access (from DB2 data source)


now you can create pivot tables in access and there is no need to import data. whenever you run query in access it will pull data directly from db2.
0
 
ghp7000Commented:
sachinwadhwa's solution isnt bad, but you will run into locking issues with linked tables, not to mention now you have another security hole to worry about, unless your network is closed.
Otherwise, lowfat or KDO are sql expert supreme, if they cant solve it, I dont know too many people who can.
0
 
LowfatspreadCommented:
try this...

SELECT c.strnam, c.stphon, b.istyln, d.colsht, b.issize,
      ,sum(Case when e.sizseq = 'sm' then a.ibhand else null end) as SM
      ,sum(Case when e.sizseq = 'med' then a.ibhand else null end) as med
      ,sum(Case when e.sizseq = 'lrg' then a.ibhand else null end) as lrg  
       sum(a.ibhand) as OnHand
  FROM invbal a
 Inner Join invmst b
    on a.inumbr = b.inumbr
 Inner Join tblstr c
    on a.istore = C.STRNUM
 Inner Join tblcol d
    on d.colcod = b.iscolr    
 Inner tblsiz e
    on b.issize = e.sizcod

 WHERE b.istyln = '152107'
   and b.iscolr = '1'

group by c.strnam, c.stphon, b.istyln, d.colsht, b.issize
order by c.strnam, c.stphon, b.istyln, d.colsht, b.issize



0
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.