Re: Pet Peeves?

Gregory Stark wrote:

>> MS-Access SQL has a TRANSFORM clause that allows for crosstab
>> queries without the need to know in advance the number of columns:
>> http://msdn.microsoft.com/en-us/library/bb208956.aspx
>
> That's puzzling. I wonder what they do about clients requesting info
> about the results. Or for that matter such queries being used in
> subqueries or anywhere else where the surrounding code needs to know
> the type of results to expect.

Using them in subqueries is just denied. Maybe there are also caveats
with the API.

>> As for Oracle, it wasn't possible until recently but now 11g has the
PIVOT
>> clause:
>>
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f

>> eatures/11g-pivot.html
>
> From this the result columns do need to be explicitly listed in
advance unless
> you're asking for the pivot to be into an xml blob which seems like a
whole
> different feature really.

Ah yes, it's still not as helpful as it should ideally, just closer. I
guess that when implementing PIVOT they would have allowed fully
dynamic columns if that was at all possible, so it's probably not.
Sure, if names/types of columns can be queried before the execute stage
but in the case of PIVOT they had to be generated by the execute stage,
that would lead to an impossible chicken-and-egg situation. Maybe
that's the reason.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org