Re: Fascinating SQL Problem

Roger Snowden (snowden_at_haven.ios.com) wrote:
: RE: Interesting Sql Problem
 

: How do you construct a view such that successive rows of detail (with the date
: value unknown) are presented as pseudo-columns in the retrieve. How do you
: report the most recent n rows as columns? Can this be done?

Yes, but not easily. Let's break down your request.

You want to retrieve in a select, an indeterminate number of columns of data. This is not possible in SQL, you have to include in your mechanism a way to determine the maximum number of columns that will exist in your new table.

You want to order the rows by the date, not by some determinate characteristic of the data. This can be done (using 'order by').

You need to deal with varying numbers of columns, not as a natural join but as some form of outer join. While this can be done, with the two preceeding this ends up with a very complex SQL statement.

When it all boils down, you end up with either a massively complex SQL statement or a fairly simple program which you might write in Pro*C (or whichever one you have available).

Pseudocode :

select primary_key,extra_info from master_table into :variable1,variable2; while (fetch from cursor)
  print variable1,variable2
  select date,value from detail_table where primary_key = :variable1

         order by date into :variable3,variable4;   while(fetch from cursor2)
    print variable3,variable4
  end while
  print carriage return
end while

In most cases the code you write will run faster and be more reliable than any SQL statement you can write.

--
Shawn Lavin
(shawn_at_mdli.com)

As always, my opinions are my own