Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, Amit.

Asked: August 01, 2017 - 6:24 pm UTC

Last updated: August 03, 2017 - 1:12 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi Masters,
I have below questions with oracle Model clause:

1. Was the name Spreadsheet initially ? When to use Model clause, does it worth learning for oracle ebs developers ? or is it totally warehousing thing ?
2. Why so many people don't use it? shall we learn Match Recognize instead ?
3. help me understanding concept of unique single reference and opposite ?
4. How to use Model with multiple tables ?
5. Please help me understanding the concept of Reference Model, why and when to use these ? is best practice is to use Reference Model instead of multiple tables?
6. Why ordering is so important ?
7. Is there any way to automate loops in it ?

and Connor said...

1) no

2) I dont know. It is just another tool in your SQL toolbox

3,4,5,6,7)

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15844208486026

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm

https://docs.oracle.com/cd/B28359_01/server.111/b28313/sqlmodel.htm

http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Few more models

Rajeshwaran Jeyabal, August 02, 2017 - 6:06 am UTC

if you are crazy about SQL Model Clause like me, then few more links to check them out.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534955800346839141#9534977800346641026
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9535749900346093304#9535827800346027051

August 03, 2017 - 1:12 am UTC

:-)

Still not clear

Amit Saroha, August 02, 2017 - 4:35 pm UTC

Hi All,
The question still remain unclear:

We should use multiple table or we should use reference Models ?
In which cases reference models should be used?

August 03, 2017 - 12:58 am UTC

It *might* seem like this is the same as a join, but dont forget, a model query can *synthesize* data, ie, a table might have

YEAR
----
2000
2001
2002

but the model query could do something like:

dimension by year
my_measure[2017] = ....

so we have "invented" data on the fly. So we might want to use this synthesized data in some way to reference other data. A join *before* the model clause commences wont work, because '2017' doesn't exist. A join *after* the model clause might not work because we want to do calculations with the lookup data whilst doing our modelling.

True Master

Amit Saroha, August 03, 2017 - 2:38 pm UTC

This is what I was looking for, thanks for clarifying it.

Truly a Master ..!!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer