#1
  1. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    792

    complex query: creating columns for a view out of a query


    I know I can do this with a script and create a table or a summary table, but it would benefit me more if I could accomplish this by creating a view and a single sql statement instead.

    I have been tasked to translate an existing access application into a web application that uses a DB2 backend.
    The query for access is:
    Code:
    TRANSFORM First([CharValue]) AS [The Value]
    SELECT [Matl], [MatGrp], [Description], First([CharValue]) AS [Total Of CharValue]
    FROM ClassData
    GROUP BY [Matl], [MatGrp], [Description]
    PIVOT [Char];
    This creates a query with a total of 31 columns.
    Matl, MatGrp, Description, Total of CharValue and the distinct contents of the Char column which is 27 separate values.

    Does anyone have any ideas on how to translate this to DB2?

    <grumble>I hate access</grumble>
  2. #2
  3. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    792
    Well I just ended up making a script and made it a static table that is updated when needed.
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    TRANSFORM and PIVOT seem to be very Access specific, I don't think that you'll find something similar in DB2.
    But splitting the char column to create fields of the view is shurely possible. I would just check for performances against your actual solution, which should be faster.
  6. #4
  7. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    792
    I know the script will help performance because it creates an actual table out of it instead of a view.
    If this was able to be done with DB2 SQL then it would be one hell of a statement and would not be the fastest thing. In the overall application I don't think it will matter that much anyway because it doesn't need to be that dynamic. Any time they need to make an updated they can just run the update script to record any changes through the system (I just hate doing things this way, seems like a waste of time).

IMN logo majestic logo threadwatch logo seochat tools logo