Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 14th, 2003, 12:02 PM
Onslaught's Avatar
Onslaught Onslaught is offline
/(bb|[^b]{2})/
Dev Shed God (5000 - 5499 posts)
 
Join Date: Nov 2001
Location: Somewhere in the great unknown
Posts: 5,163 Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level) 
Time spent in forums: 6 Days 1 h 34 m 20 sec
Reputation Power: 791
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>

Reply With Quote
  #2  
Old January 14th, 2003, 10:56 PM
Onslaught's Avatar
Onslaught Onslaught is offline
/(bb|[^b]{2})/
Dev Shed God (5000 - 5499 posts)
 
Join Date: Nov 2001
Location: Somewhere in the great unknown
Posts: 5,163 Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level) 
Time spent in forums: 6 Days 1 h 34 m 20 sec
Reputation Power: 791
Well I just ended up making a script and made it a static table that is updated when needed.

Reply With Quote
  #3  
Old January 15th, 2003, 03:18 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 8th Plane (8500 - 8999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,527 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 1 Day 3 h 19 m 49 sec
Reputation Power: 537
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.

Reply With Quote
  #4  
Old January 15th, 2003, 08:08 AM
Onslaught's Avatar
Onslaught Onslaught is offline
/(bb|[^b]{2})/
Dev Shed God (5000 - 5499 posts)
 
Join Date: Nov 2001
Location: Somewhere in the great unknown
Posts: 5,163 Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level)Onslaught User rank is Major General (70000 - 90000 Reputation Level) 
Time spent in forums: 6 Days 1 h 34 m 20 sec
Reputation Power: 791
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).

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > complex query: creating columns for a view out of a query

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap