Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Question regarding model clause

747323
Member Posts: 2
{size:14}
Is there any way that I can make the following query as I meant?
{size}
I wish to make the result something like following.
{size}
Any suggestion?
Thanks in advance.
{size}
Is there any way that I can make the following query as I meant?
{size}
SELECT RNK, FIRST, SECOND FROM ( SELECT 1 RNK FROM DUAL UNION ALL SELECT 2 RNK FROM DUAL ) MODEL DIMENSION BY ( RNK ) MEASURES ( 0 FIRST, 0 SECOND ) RULES ( FIRST[1] = 1, FIRST[RNK > 1] = SECOND[CV(RNK) - 1] + 1, SECOND[ANY] = FIRST[CV()] + 2 ){size:14}
I wish to make the result something like following.
{size}
RNK FIRST SECOND ---------- ---------- ---------- 1 1 3 2 4 6{size:14}
Any suggestion?
Thanks in advance.
{size}
Answers
-
Please explain the business rules and provide a version number.
-
Hi,
Damorgan is right. If you want a solution that works in your version, why not say what your version is?
Always explain what you're trying to do. Posting the results from your sample data, like you did, is great, but there's always a chance someone will give you a solution that happens to work perfectly on your sample data, but won;t work on your real data. Guessing is not a very efficient way to work. Don't make people guess what you want.
To get the results you requested, you don't need MODEL at all;SELECT rnk , (3 * rnk) - 2 AS first , (3 * rnk) AS second FROM ( SELECT 1 AS rnk FROM dual UNION ALL SELECT 2 AS rnk FROM dual ) ;
If you really want to use MODEL, then make a special case for SECOND[1], like you did for FIRST[1].
Also, use AUTOMATIC ORDER, since there are dependencies among the rulesSELECT RNK, FIRST, SECOND FROM ( SELECT 1 RNK FROM DUAL UNION ALL SELECT 4 RNK FROM DUAL UNION ALL SELECT 2 RNK FROM DUAL ) MODEL DIMENSION BY ( RNK ) MEASURES ( 0 FIRST, 0 SECOND ) RULES AUTOMATIC ORDER ( FIRST [1] = 1, SECOND[1] = 3, FIRST [RNK > 1] = SECOND[CV(RNK) - 1] + 1, SECOND[RNK > 1] = FIRST[CV()] + 2 ) ;
or just intialze row 1 the way you want it:SELECT RNK, FIRST, SECOND FROM ( SELECT 1 RNK FROM DUAL UNION ALL SELECT 2 RNK FROM DUAL UNION ALL SELECT 3 RNK FROM DUAL ) MODEL DIMENSION BY ( RNK ) MEASURES ( 1 FIRST, 3 SECOND ) RULES AUTOMATIC ORDER ( FIRST [RNK > 1] = SECOND[CV(RNK) - 1] + 1, SECOND[RNK > 1] = FIRST[CV()] + 2 ) ;
-
Thanks guys, especially Frank.
It worked like a charm.
Great!!
Actually, I was questioning about the dependency inside the model clause.
Anyway, thanks for your quick responses, again.
By the way, I'm using 11g. -
I like recursive with clause B-)
Haha I have used PostgreSQL8.4 because I do not have Oracle11gR2 :8}with recursive rec(rnk,FIRST,SECOND) as( select 1,1,3 union all select rnk+1,second+1,(second+1)+2 from rec where rnk+1<= 3) select*from rec; rnk | first | second -----+-------+-------- 1 | 1 | 3 2 | 4 | 6 3 | 7 | 9
This discussion has been closed.