January 14th, 2003, 01:02 PM
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:
This creates a query with a total of 31 columns.
TRANSFORM First([CharValue]) AS [The Value]
SELECT [Matl], [MatGrp], [Description], First([CharValue]) AS [Total Of CharValue]
GROUP BY [Matl], [MatGrp], [Description]
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>
January 14th, 2003, 11:56 PM
Well I just ended up making a script and made it a static table that is updated when needed.
January 15th, 2003, 04:18 AM
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.
January 15th, 2003, 09:08 AM
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).