Home » SQL & PL/SQL » SQL & PL/SQL » Model clause (10g)
Model clause [message #596357] Sat, 21 September 2013 08:07 Go to next message
m.abdulhaq
Messages: 136
Registered: April 2013
Location: Ajman
Senior Member
i have a table where there is a sales of one item for 2 years , i am trying to add these two years total using model clause , as i want to learn model clause , its giving a error as below.


CREATE TABLE ISSI_IT
(
  IT   VARCHAR2(20 BYTE)                        NOT NULL,
  DT   VARCHAR2(4 BYTE),
  QTY  NUMBER
)


INSERT INTO ISSI_IT ( IT, DT, QTY ) VALUES ( 
'11AST01001200000', '2013', 34); 
INSERT INTO ISSI_IT ( IT, DT, QTY ) VALUES ( 
'11AST01001200000', '2012', 24); 
COMMIT;


SELECT IT,DT,QTY FROM ISS_IT
MODEL
DIMENSION BY (IT,DT)
MEASURES ((QTY) S)
RULES UPSERT
(S['11AST01001200000',2014] = S['11AST01001200000',2012]+S['11AST01001200000',2013])

ORA-00934 group function is not allowed.

icon2.gif  Re: Model clause [message #596359 is a reply to message #596357] Sat, 21 September 2013 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> SELECT IT,DT,QTY FROM ISSI_IT
  2  MODEL
  3  DIMENSION BY (IT,DT)
  4  MEASURES (QTY)
  5  RULES UPSERT
  6  (qty['11AST01001200000',2014] = qty['11AST01001200000',2012]+qty['11AST01001200000',2013])
  7  /
IT                   DT          QTY
-------------------- ---- ----------
11AST01001200000     2013         34
11AST01001200000     2012         24
11AST01001200000     2014         58

3 rows selected.
Re: Model clause [message #596361 is a reply to message #596357] Sat, 21 September 2013 09:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
The issue is with the alias which you ambiguously specified in the select statement. Just replace QTY with S -

SQL> SELECT IT,DT,S FROM ISSI_IT
  2  MODEL
  3  DIMENSION BY (IT,DT)
  4  MEASURES ((QTY) S)
  5  RULES UPSERT
  6  (S['11AST01001200000',2014] = S['11AST01001200000',2012]+S['11AST01001200000',2013]);
 
IT                   DT            S
-------------------- ---- ----------
11AST01001200000     2013         34
11AST01001200000     2012         24
11AST01001200000     2014         58


Also, in your select statement, the table name is incorrect(might be a typo).

Regards,
Lalit
icon13.gif  Re: Model clause [message #596365 is a reply to message #596361] Sat, 21 September 2013 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Also, in your select statement, the table name is incorrect(might be a typo).


Hopefully you are there to tell what everyone can immediately see, sure it is worth to mention it... for the blind ones.

http://www.orafaq.com/forum/mv/msg/188003/586788/#msg_586788



[Updated on: Sat, 21 September 2013 16:04]

Report message to a moderator

Re: Model clause [message #596373 is a reply to message #596359] Sat, 21 September 2013 22:47 Go to previous messageGo to next message
m.abdulhaq
Messages: 136
Registered: April 2013
Location: Ajman
Senior Member
thank you Mr.Michael and Mr.Lalit kumar for the help , please can someone refer me to a good article which explains model clause with examples.Also like to know where i can get the schema that is mentioned in Oracle datawarehouse guide chapter 22 , which explains about model clause as i will practice using that star schema.
icon14.gif  Re: Model clause [message #596374 is a reply to message #596373] Sun, 22 September 2013 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can install the sample schemas using OUI you have an option for this. Have a look at @?/demo/schema/mksample.sql to check if you have not already the scripts in your Oracle home directory.
The command to install the schemas is then:

@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temp_tablespace log_file_directory/


Database Sample Schemas details them.

[Updated on: Sun, 22 September 2013 01:16]

Report message to a moderator

Re: Model clause [message #596379 is a reply to message #596374] Sun, 22 September 2013 03:27 Go to previous message
m.abdulhaq
Messages: 136
Registered: April 2013
Location: Ajman
Senior Member
thanks very much.
Previous Topic: How Rowids are assigned to rows by Oracle?
Next Topic: Time Datatype
Goto Forum:
  


Current Time: Sat Dec 27 14:11:18 CST 2014

Total time taken to generate the page: 0.04990 seconds