Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert Crosstab access query to DB2 sql query.

Posted on 2006-05-23
9
Medium Priority
?
6,301 Views
Last Modified: 2008-05-15
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.


0
Comment
Question by:Smitty200
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 16743572
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
 

Author Comment

by:Smitty200
ID: 16743603
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
 
LVL 13

Expert Comment

by:ghp7000
ID: 16745792
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1500 total points
ID: 16745957
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16745969
>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
 

Author Comment

by:Smitty200
ID: 16746937
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
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 16749080
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
 
LVL 13

Expert Comment

by:ghp7000
ID: 16751826
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16758456
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 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