Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Syntax for simple (?) pivot with MODEL clause

Re: Syntax for simple (?) pivot with MODEL clause

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 13 Aug 2007 16:02:58 -0800
Message-ID: <46c0e322$1@news.victoria.tc.ca>


dba_222_at_yahoo.com wrote:
: OK, I'm back. No emergencies today.

: Our data changes on a daily, weekly and monthly basis.
: So, hardcoding values is not an option.

: CASE and DECODE would require hardcoding; that you
: know exactly what the values will be. If new rows with
: new and different values were entered into the field,
: you would have to re hardcode the query.

: I had looked at PSOUG actually.
: http://www.psoug.org/reference/model_clause.html

: Where at least some of the examples were the same as from:
: http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm

: Both examples required hardcoding the value:

: RULES (sales['Bounce', 2005] = 100 + MAX(sales)['Bounce',
: year BETWEEN 1998 AND 2002])

: ie. 'BOUNCE', refers to product = 'BOUNCE'

: With 10G's new features, is it possible to do this kind of thing,
: dynamically getting a different number of columns back?
: In particular with the MODEL clause?

: Perhaps a new feature in Oracle 11?

: What is the secret please?

You could try the SQL SPREADSHEET options, I haven't used them but they look interesting, maybe that would help.

You can also do what you wish using Oracle Reports. Look up report help, search for "MATRIX", read the details on "matrix reports"

If you can ignore the vertical orientation of the display, then you can probably do what you want with a plain old group by expression.

You could use PL/SQL to generate the required SQL select, and then either EXECUTE IMMEDIATE , or spool the select statement into an SQL file and run that.

You can use SQL to generate the required SQL select, and then spool the select statement into an SQL file and run that.

You could probably use Excel, it can connect directly to the database and do many fancy things (as long as your data does not have too many rows for Excel, - while I think of it, I think that is also a limitation with the Oracle SQL SPREADSHEET capabaility, it too has some kind of maximum on what it can handle).

Oracle has other tools besides SQL*Plus and Reports that have various useful reporting capabilities that you could investigate.

$0.10 Received on Mon Aug 13 2007 - 19:02:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US