How can I change the shape of a query's results

Posted on 2008-10-03
Last Modified: 2013-12-18
I am starting a project where an old system is being updated. The code is all VB (not .net) and we will be using ADODB recordsets etc (mostly becasuse the analysis software is certified and recoding it for say .net would need it to be recertified) An odbc connection to an oracle express 10g system for development and a full blown 9 for deployment.

Currently the software reads log files of the format:
     time stamp, value, value, value
     time stamp, value, value, value
The three values are for different devices, and their order is important, but is defined by another file (there are also more than 3, up to 20 in some cases). Each line has a diffeent timestamp, appearing in time order

We are now storing the raw data in a database table with:
   device id, timestamp, value
This table cannot be altered (and seems bad design to add multiple id and values as that would limit future work)

Most of my SQL up to this point has been pretty basic, so my initial suggestion of a series of:
   select value from table where device = deviceID
and then manually iterating through my set of recordsets to build a pseudo-logfile for the app to read (probably by building up a row and senting it to the target for the original read statement).

Is there a more elegant way of getting from a vertical table to a horizontal row?
ie from a table structure of:
      device id, timestamp, value
to a recordset of:
   timestamp, value, value,
where the value,value order would be defined into the query?
Question by:dajm
  • 3
  • 2

Accepted Solution

Dorfs earned 125 total points
ID: 22632499
you'd need to join the tables but it is gonne be a long query depending on the number of tables that need to be joined

Author Comment

ID: 22632692
Hi Dorfs.
Thanks for the suggestion. This is the way to go with multiple tables, but I only have 1 raw data table.

I have tried a join but although the shape seems right, the data isn't. I know there are 96 values per device but I'm getting much much more.

I will play about with the join to see if I can get it to work.

Clocking off early today (its my Birthday and my folks flew in for it, so I might not be back til Monday)

Expert Comment

ID: 22633010
Happy birthday,

the way I see it its rather complex to do with a query but you basic want to get all the values from the devices on a certain timestamp the eazyest way to do this is with iterating and i'm fairly certain oracle will allow you to make some kind of function that does this for you, however I'm didn't work oracle enough to help you with making one.

The join makes every possible combination which than needs to be filtered with the" where" statements and in this case I really don't see it happening to get just one row that hasn't already been displayed and than display it. you could try to look into subquerys but I doubt its going to come up with something usefull.

I'd recommend looking into functions for oracle to basicly do the same thing your software is doing but than on the database level.

Author Comment

ID: 22649823
Hi Dorfs,
I got the join working correctly and its is definitely the answer to my questiion - it does give the right shape. The complexity of building the query isn't really a concern (the analysis may take 10's minutes so the overhead of building a complex query isn't a worry)
Sadly (for me) I don't think its the answer to my problem. I need to supply a timestamp to the analysis program and thought I would simply get the timestamp for the first device and its value and then simply the values for the other devices. I can then sort on the first device's timestamp to get its set in order, but cannot be sure the other joined devices are in the correct order...

For posterity,
instead of a series of
   select timstamp, value from table where device=id and time=range order by time
and then manually iterating over the set of results to build my array of values for a timestamp, the query:
   select t1.timestamp, t1.value, t2.value from table t1 inner join table t2 on t1.timestamp= t2.timestamp where t1.device=id1 and t2.device=id2 and trunc(t1.timestamp) = to_date('12-nov-06','dd-mon-yy') order by t1.timestamp;

Author Closing Comment

ID: 31502701
Thanks for the quick response and apologies for my (albeit, only once a year) pre-occupation.
Sadly it looks like i will have to stick with my first idea and damn the elegance.

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 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