9 Replies Latest reply: Jun 21, 2013 10:48 AM by kendenny RSS

    Abnormal(?) rownum

    swapnil kambli

      Dear all,

      Could you please help me understand how's  the last column of first and last row calculated?

       

      SQL> select rownum,rownum-1,rownum-2,rownum||rownum-1||rownum-2 from dual connect by level < 11;
          ROWNUM   ROWNUM-1   ROWNUM-2 ROWNUM||ROWNUM-1||ROWNUM-2
      ---------- ---------- ---------- --------------------------
               1          0         -1                         99
               2          1          0                        210
               3          2          1                        321
               4          3          2                        432
               5          4          3                        543
               6          5          4                        654
               7          6          5                        765
               8          7          6                        876
               9          8          7                        987
              10          9          8                     100908
      10 rows selected.
      
      SQL> select * from v$version ;
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0      Production
      TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      
      SQL> !uname -a
      AIX svp0150pdv 1 6 00F6D7D54C00
      

       

      Thanks,

      Swapnil.

        • 1. Re: Abnormal(?) rownum
          Greg Spall
          select rownum,rownum-1,rownum-2,
                 (rownum||(rownum-1)||rownum)-2
          from dual connect by level < 11;
          
          

           

          brackets help see it.

           

          so:  (1 || 0 || 1) - 2

          • 2. Re: Abnormal(?) rownum
            swapnil kambli
            SQL> select rownum,rownum-1,rownum-2,(rownum||(rownum-1)||rownum)-2   from dual connect by level < 11;
                ROWNUM   ROWNUM-1   ROWNUM-2 (ROWNUM||(ROWNUM-1)||ROWNUM)-2
            ---------- ---------- ---------- ------------------------------
                     1          0         -1                             99
                     2          1          0                            210
                     3          2          1                            321
                     4          3          2                            432
                     5          4          3                            543
                     6          5          4                            654
                     7          6          5                            765
                     8          7          6                            876
                     9          8          7                            987
                    10          9          8                          10908
            10 rows selected.
            

            That did not worked but below did,Any idea what is going on there?

             

            SQL> select rownum,rownum-1,rownum-2,(rownum)||(rownum-1)||(rownum-2) from dual connect by level < 11;
                ROWNUM   ROWNUM-1   ROWNUM-2 (ROWNUM)||(ROWNUM-1)||(ROWNUM-2)
            ---------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------
                     1          0         -1 10-1
                     2          1          0 210
                     3          2          1 321
                     4          3          2 432
                     5          4          3 543
                     6          5          4 654
                     7          6          5 765
                     8          7          6 876
                     9          8          7 987
                    10          9          8 1098
            10 rows selected.
            
            • 3. Re: Abnormal(?) rownum
              Greg Spall

              Sorry, didn't read your post thoroughly

               

              swapnilkambli wrote:

               

              That did not worked but below did,Any idea what is going on there?

               

               

              Yes, I know exactly what's going on .. the order of operation is confusing you ..

               

              what are you trying to accomplish??

               

              Oracle is doing the string operations first, so:

               

              a+b||c+d||e+f
              
              
              

              turns into:

               

              a + (b||(c+d)||e) + f
              
              
              

               

              [edit]

              if you're just trying to string them . use more brackets:

               

              (rownum) || (rownum-1) || (rownum-2)

              • 4. Re: Abnormal(?) rownum
                swapnil kambli

                Thanks Greg but see below:

                 

                SQL> select (rownum||(rownum-1)||rownum)-2,(rownum)||(rownum-1)||(rownum-2) from dual connect by level < 11;
                (ROWNUM||(ROWNUM-1)||ROWNUM)-2 (ROWNUM)||(ROWNUM-1)||(ROWNUM-2)
                ------------------------------ ------------------------------------------------------------------------------------------------------------------------
                                            99 10-1
                                           210 210
                                           321 321
                                           432 432
                                           543 543
                                           654 654
                                           765 765
                                           876 876
                                           987 987
                                         10908 1098
                10 rows selected.
                SQL>
                
                • 5. Re: Abnormal(?) rownum
                  Frank Kulash

                  Hi,

                   

                  When you mix operators in the same expression, use parentheses to group them the way you want.  Otherwise, they will be grouped according to rules which you may not know about or understand.

                  The || operator has the same precedence as the binary  - operator, so the expression you wrote (n1) is equivalent to n2, not to v3.

                   

                  select  rownum

                  ,       rownum - 1                      AS r1

                  ,       rownum - 2                      AS r2

                  ,       rownum || rownum-1 || rownum-2  AS n1

                  ,       ( ( (rownum || rownum)

                            - 1

                            ) || rownum

                          - 2

                          )                               AS n2

                  ,       rownum || (rownum - 1)

                                 || (rownum - 2)          AS v3

                  from    dual

                  connect by level < 11

                  ;

                   

                  Output:

                   

                  ROWNUM    R1    R2      N1      N2 V3

                  ------ ----- ----- ------- ------- ----------

                       1     0    -1      99      99 10-1

                       2     1     0     210     210 210

                       3     2     1     321     321 321

                       4     3     2     432     432 432

                       5     4     3     543     543 543

                       6     5     4     654     654 654

                       7     6     5     765     765 765

                       8     7     6     876     876 876

                       9     8     7     987     987 987

                      10     9     8  100908  100908 1098

                   

                   

                  See the SQL language manual for the precedence of operators.  http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators001.htm#sthref858

                  • 6. Re: Abnormal(?) rownum
                    Greg Spall

                    Yeah .. what's "rownum-2"?  when rownum = 1?

                    answer: -1

                     

                    what's 1 || 0 || -1 ??

                    10-1

                    Looks fine to me

                    • 7. Re: Abnormal(?) rownum
                      swapnil kambli

                      Thanks Greg..not trying to accomplish anything...I was just killing time..weekend started here... no one in office and no work

                      • 8. Re: Abnormal(?) rownum
                        Martin Preiss

                        concat is a string operator: http://docs.oracle.com/cd/E11882_01/server.112/e26088/operators003.htm#i997789 and its a question of operator priorities: select 1||1-1||1-2 from dual;

                         

                        1||1-1||1-2

                        -----------

                                 99

                         

                        So it is correct - but it looks strange.

                         

                        Regards

                         

                        Martin

                        • 9. Re: Abnormal(?) rownum
                          kendenny

                          It appears that its doing (rownum || (rownum-1) || rownum) -2

                          So for rownum = 1 it's (1||0||1)-2 so 101-2=99

                          For rownum=10 it's (10||9||10)-2 so 10910-2=10908