Link to home
Start Free TrialLog in
Avatar of dajm
dajm

asked on

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

Hello.
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?
ASKER CERTIFIED SOLUTION
Avatar of Dorfs
Dorfs

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dajm
dajm

ASKER

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)
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.
Avatar of dajm

ASKER

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;
Avatar of dajm

ASKER

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.
Cheers,Dermot