Forum Stats

  • 3,837,059 Users
  • 2,262,222 Discussions
  • 7,900,194 Comments

Discussions

Question regarding model clause

747323
747323 Member Posts: 2
edited Jan 20, 2010 5:21AM in SQL & PL/SQL
{size:14}
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}
Tagged:

Answers

  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    Please explain the business rules and provide a version number.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,212 Red Diamond
    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 rules
    SELECT  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
          )
    ;
  • 747323
    747323 Member Posts: 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.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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.