|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
January 14th, 2003, 12: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: 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>
|
|
#3
January 15th, 2003, 03: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.
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins
|
|
#4
January 15th, 2003, 08: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).
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > complex query: creating columns for a view out of a query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
