Upsert on multiple column dimensions
A reader, November 12, 2004 - 6:30 pm UTC
Hi Tom,
Could you explain the following:
I get two rows with this:
SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, 2] = Y[1, 1]
)
However only one row for this (I expected two):
SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, any] = Y[1, 1]
)
Thanks a lot.
November 13, 2004 - 9:52 am UTC
well, the first one said "for [x1=2,x2=2], y will be assigned the value that y[x1=1,x2=1] had"
the second says "for [x1=2, any value for x2], y will be ....."
but in the second case, there weren't any "any's" to be found -- none are made "up" for it (the only possible answers in my opinion would be either the empty set as it did OR an *infinite* set as there are an infinite set of X2 values possible -- just none actually present)
ORA-33262: Analytic workspace EXPRESS does not exist
gia, January 12, 2006 - 7:34 pm UTC
Hello Tom,
We have error ORA-33262 when using SQL_MODEL. Do we need to create AW EXPRESS?
Thanks
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jan 13 10:37:06 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> aw create awwork
SP2-0734: unknown command beginning "aw create ..." - rest of line ignored.
SQL> aw list
SP2-0042: unknown command "aw list" - rest of line ignored.
SQL> SELECT STATE, CCY, EXPECTED_DT, REPAY, INT_RATE,
2 COF_RATE
3 FROM LDV_CONTRACT_WGT_RATE
4 MODEL RETURN UPDATED ROWS
5 PARTITION BY (state,ccy)
6 DIMENSION BY (expected_dt)
7 MEASURES (repay,int_rate,cof_rate)
8 RULES
9 ( repay [to_date('31/12/9999','dd/mm/yyyy')] = SUM(repay) [CV(expected_dt)]
10 )
11 order by state,ccy,expected_dt
12 /
FROM LDV_CONTRACT_WGT_RATE
*
ERROR at line 3:
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP
technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
January 13, 2006 - 11:03 am UTC
I'd see the error message and do what it says :)
Script ran,
gia, February 14, 2006 - 1:48 am UTC
Hi Tom,
DBA ran script (he does NOT know aboput this script ??) to activate analytical option in 10g database. Problem resolved.
Thank you for your help.
Bryan, July 14, 2006 - 9:03 am UTC
Can you tell me what script your DBA ran to fix this issue( Create a workspace??)??..I am getting the same error??..
Model clause links
Soumadip, February 27, 2012 - 10:33 am UTC
MODEL articles
Duke Ganote, February 28, 2012 - 8:18 am UTC
February 28, 2012 - 9:53 am UTC
thanks! appreciate that.
2003 Oracle OpenWorld presentation?
Duke Ganote, March 26, 2012 - 9:30 am UTC
I think found the 2003 OpenWorld presentation on the MODEL clause by Andy Witkowski and some other guy.
download.oracle.com/owsf_2003/40166.ppt
March 26, 2012 - 11:21 am UTC
I remember that ;)
Model clause comparison with others
Asim, August 04, 2022 - 11:39 am UTC
I am trying to understand Model clause. I have following questions.
1.
Is it true that if you use MODEL clause you cannot use analytic functions in SELECT list?
2.
If the above is true, can we say that every query with analytical functions in SELECT list can be transformed into equivalent query with MODEL clause and without analytic functions in SELECT list?
3.
Also is the vice versa also true? ie every query with MODEL Clause can be transformed into equivalent query without MODEL clause and with analytical functions in SELECT list?
4.
Is the oracle's OLAP DML(or should we say MOLAP DML), is oracle's thing equivalent/compareable to the MDX (multidimentional expressions/queries) of MS SQL Server analysis services (OLAP/MOLAP).
5.
Is the MODEL clause, is Oracle's relational (ROLAP) thing equivalent/comparable to the MDX (multidimentional expressions/queries) of MS SQL Server analysis services (OLAP/MOLAP), that query MS SSAS's MOLAP cubes.?
6.
Is the MODEL clause, is Oracle's relational (ROLAP) thing equivalent/comparable to the oracle's OLAP DML that query oracle's OLAP/MOLAP cubes.
Thanks
August 04, 2022 - 1:52 pm UTC
1. Correct
If your query has a MODEL clause, then the query's SELECT and ORDER BY lists cannot contain aggregates or analytic functions https://docs.oracle.com/en/database/oracle/oracle-database/21/dwhsg/sql-modeling-data-warehouses.html#GUID-AD6A9B81-492F-4724-9517-7758C4A98365 2. You can use analytic functions within MODEL itself, so a strict interpretation of your question would say this is true.
If you're asking if you can convert every analytic function to equivalent rules in MODEL, then you probably can; I'm not 100% certain.
You can always use MODEL in a subquery and an analytic on its results in an outer query.
3. No. The MODEL clause can generate new rows and change the values of the measures. Analytic functions can't do this.
4-6 - I don't know know about OLAP processing in either Oracle Database or SQL Server to answer this
Addendum to above
Asim, August 04, 2022 - 11:59 am UTC
7.
What is the order of processing of MODEL clause, I think, its like FROM JOIN, WHERE, GROUP BY, HAVING, WINDOW, MODEL, ORDER BY, FETCH? am i right? May be the WINDOW clause is not allowed with MODEL clause
8.
I see that MODEL clause can add rows to the query result set, it can also update the values of query result set. Can MODEL clause add columns to the query result set?
8.
August 04, 2022 - 1:55 pm UTC
7. You can't combine GROUP BY, HAVING or WINDOW with MODEL in the same subquery. Otherwise, this is correct.
8. Yes, you can define new columns in the DIMENSION/MEASURES clauses.
More
Asim, August 04, 2022 - 8:19 pm UTC
9.
I can see MODEL can reduce the no of rows of the result set ie RETURN UPDATED ROWS ONLY, can it also reduce the no of columns of the result set?
10.
What is the processing order of PIVOT clause
11.
If MODEL clause is used, can PIVOT clause be used in the same query(or to be precise in the same SELECT).
12.
Can I say that PIVOT and MODEL are the only clauses that can add columns to a query result set in addition to SELECT list.
August 05, 2022 - 12:56 pm UTC
9 MODEL returns the columns listed in the PARTITION, DIMENSION, and MEASURES clauses. If you don't list a table's columns in one of these then it's not in the results. So yes.
10 If you have questions about PIVOT, please submit a new question about this.
11 You can PIVOT before MODEL
12 No - MATCH_RECOGNIZE can define new columns
7
Asim, August 07, 2022 - 11:06 am UTC
7. But as we know PIVOT needs aggregate function and MODEL doesnt allow that. Then how is it possible.
Can you please gie an example of running query with PIVOT and MODEL togather in one SELECT (NOT in one query with outer and inner select).
August 08, 2022 - 1:20 am UTC
What is the issue with "NOT in one query with outer and inner select"
That's like saying "I want to count the records in a table but without using COUNT function".
If the facilities are available, then why would you not use them? That always befuddles me.
In fact, most often my first recommendation for developers building complicated SQL statements is to break it up using WITH because you *want* to have it compartmentalized to assist the next person coming along who will be maintaining it.
Sorry its 11 not 7
Asim, August 07, 2022 - 11:08 am UTC
Sorry it 11 not 7 in above post
Just to know
Asim, August 08, 2022 - 4:36 pm UTC
Because I just wanted make my concepts clear, and therefore wanted to know that PIVOT and MODEL can exist in the same SELECT or not.
Also I want to know if the following can exist in the same SELECT or not
a) MODEL and MATCH_RECOGNIZE
b) PIVOT and MATCH_RECOGNIZE
August 08, 2022 - 4:47 pm UTC
Rather than keep asking us what's possible - what have you tried?