14 Replies Latest reply on Nov 12, 2019 2:28 PM by Ranagal

    question on reverse

    user1014019

      Hi Team,

       

      I have a question on below query.

       

      ID     QID       QNAME

      1       12          SEETA

      2       13           GEETA

      3       14           RAJ

      4       15           UMBER

      5       16           MEERA

      6       17           SMRITI

       

      I wanted the output as shown below

       

      QID     QNAME

      21        MEERA

      31        SMRITI

      41        SEETA

      51        GEETA

      61        RAJ

      71        UMBER

       

      We will use reverse function to reverse the QID Values but need help on how will we interchange QNAME values.

      Appreciate your help.

        • 1. Re: question on reverse
          mathguy

          The question is not about reverse, is it? You just said that you are going to use that function, but your only question is about permuting the QNAME's relative to the (reversed) QID's.

           

          You didn't explain what you are trying to do. Are you looking for a random reassignment? I will show one way you can do that below.

           

          Before that, though - did you know that REVERSE is not documented and not supported by Oracle? If you are doing this for some meaningful purpose (and not just simply as a game, or for learning purposes), you would do very well to reconsider its use.

           

          OK, now to the shuffling of names. You didn't post usable test data, so instead I will use the EMP table in the SCOTT schema. I will use the EMPNO and ENAME columns - I will show how you can randomly reassign the names to the (unchanged) employee numbers. (No REVERSE in my example - that's a separate issue you can take care of easily in the outer SELECT, if you insist on using REVERSE.)

           

          Here is what you will find in the EMP table:

           

          select empno, ename from scott.emp;

           

            EMPNO ENAME

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

             7369 SMITH  

             7499 ALLEN  

             7521 WARD    

             7566 JONES  

             7654 MARTIN  

             7698 BLAKE  

             7782 CLARK  

             7788 SCOTT  

             7839 KING    

             7844 TURNER  

             7876 ADAMS  

             7900 JAMES  

             7902 FORD

             7934 MILLER

           

           

          The query to shuffle names (while keeping EMPNO unchanged):

           

          with

            e (empno, ename, rn, shuffle) as (

              select empno, ename, rownum, row_number() over (order by dbms_random.value())

              from   scott.emp

            )

          select e1.empno, e2.ename

          from   e e1 inner join e e2 on e1.rn = e2.shuffle

          ;

           

            EMPNO ENAME

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

             7369 MARTIN  

             7499 ALLEN  

             7521 SMITH   

             7566 MILLER  

             7654 ADAMS  

             7698 BLAKE  

             7782 SCOTT  

             7788 CLARK  

             7839 TURNER  

             7844 FORD    

             7876 JONES  

             7900 WARD    

             7902 JAMES  

             7934 KING

           

          Of course, if you try this, you may get a different shuffling - the new association between employee numbers and names is indeed random.

          • 2. Re: question on reverse
            Gaz in Oz

            We will use reverse function to reverse the QID Values but need help on how will we interchange QNAME values.

            I hope you don't mean the undocumented, unsupported Oracle reverse function!

            Write your own.

            • 3. Re: question on reverse
              BEDE

              Just how does the new QID result from the source data? Is thare an algorithm? Or is it that you want just some random number?

              • 4. Re: question on reverse
                Stew Ashton

                It has been two years since you marked a question as answered. Why is that?

                 

                Anyway, here is a "solution" that uses only documented functions and happens to produce the output you wanted. It appears that you want to reverse the QID value and shift the QNAME value down two rows in a circular fashion; that means the last two values move up to the top.

                 

                UPDATE: my code to shift the QNAME value down 2 only worked with 6 rows. I have updated line 24 below and it now works with 7 rows as well...

                UPDATE 2: in later replies, two people pointed out simpler, cleaner ways to do line 17. I chose Ranagal's alternative.

                 

                with data(ID,QID,QNAME) as (
                  select 1, 12, 'SEETA' from dual union all
                  select 2, 13, 'GEETA' from dual union all
                  select 3, 14, 'RAJ' from dual union all
                  select 4, 15, 'UMBER' from dual union all
                  select 5, 16, 'MEERA' from dual union all
                  select 6, 17 , 'SMRITI' from dual
                )
                , data_with_max(rn,QID,QNAME,max_rn) as (
                  select row_number() over(order by id),
                    qid, qname,
                    count(*) over()
                  from data
                )
                select
                (
                  select listagg(substr(qid,level,1)) within group(order by level desc)
                  from dual
                  connect by level <= length(qid)
                ) RQID,
                qname,
                nth_value(
                  qname,
                  mod(max_rn+rn-3, max_rn)+1
                ) over(
                  order by rn rows between unbounded preceding and unbounded following
                ) new_qname
                from data_with_max;
                
                RQID   QNAME    NEW_QNAME   
                21     SEETA    MEERA        
                31     GEETA    SMRITI       
                41     RAJ      SEETA        
                51     UMBER    GEETA        
                61     MEERA    RAJ          
                71     SMRITI   UMBER 
                

                 

                Best regards,

                Stew Ashton

                • 5. Re: question on reverse
                  BrunoVroman

                  Hello Gaz,

                   

                  << the undocumented, unsupported Oracle reverse function!>>

                   

                  Wow! I'm very surprised... I hardly ever use this function (and probably never "seriously") but I was absolutely not aware it was not "official"!

                  How can this be -I mean to have such a function undocumented, I expect either to have it and documented or not to have it at all- ?

                   

                  Amazed regards,

                   

                  Bruno Vroman

                  • 6. Re: question on reverse
                    Stew Ashton

                    BrunoVroman wrote:

                    ...

                    How can this be -I mean to have such a function undocumented, I expect either to have it and documented or not to have it at all- ?

                    Do we really "have it" if it is not documented? The only way we could not access it is if it did not exist at all, which means Oracle could not use it either.

                     

                    By the way, I now see UTL_RAW.REVERSE is documented! It clearly reversed bytes, not characters, whereas my LISTAGG-based solution works on characters.

                     

                    select level lvl,
                    utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(level))) reversed
                    from dual
                    connect by level <= 20;
                    
                    LVL  REVERSED   
                      1  1           
                      2  2           
                      3  3           
                      4  4           
                      5  5           
                      6  6           
                      7  7           
                      8  8           
                      9  9           
                     10  01          
                     11  11          
                     12  21          
                     13  31          
                     14  41          
                     15  51          
                     16  61          
                     17  71          
                     18  81          
                     19  91          
                     20  02 
                    

                     

                    Regards,

                    Stew

                    • 7. Re: question on reverse
                      Solomon Yakobson

                      Function REVERSE is internal function used by Oracle when creating REVERSE indexes and it reverses bytes, not characters so we might get unexpected result with multi-byte character sets.

                       

                      SY.

                      • 8. Re: question on reverse
                        Ranagal

                        Hi Stew,

                         

                        Stew wrote:

                         

                        with data(ID,QID,QNAME) as

                        (

                        select 1, 12, 'SEETA' from dual union all

                        select 2, 13, 'GEETA' from dual union all

                        select 3, 14, 'RAJ' from dual union all

                        select 4, 15, 'UMBER' from dual union all

                        select 5, 16, 'MEERA' from dual union all

                        select 6, 17 , 'SMRITI' from dual

                        ),

                        data_with_max(rn,QID,QNAME,max_rn) as

                        (

                        select  row_number() over(order by id),

                          qid, qname,

                          count(*) over()

                        from data

                        )

                        select

                        (

                        select  listagg(substr(qid,length(qid)-level+1,1)) within group(order by level)

                        from  dual

                        connect by level <= length(qid)

                        ) RQID,

                        qname,

                        nth_value

                        (

                          qname,

                          mod(rn+3,max_rn)+1

                        )

                        over

                        (

                          order by rn rows between unbounded preceding and unbounded following

                        ) new_qname

                        from data_with_max;

                         

                        Can the highlighted one be replaced just with below ?

                         

                        select  listagg(substr(qid,level,1)) within group(order by level desc)

                         

                        I mean what might go wrong with my approach ?

                         

                        Regards,

                        Ranagal

                        • 9. Re: question on reverse
                          Solomon Yakobson

                          Solomon Yakobson wrote:

                           

                          Function REVERSE is internal function used by Oracle when creating REVERSE indexes and it reverses bytes, not characters so we might get unexpected result with multi-byte character sets.

                           

                          SY.

                          Just to clarify, we might get unexpected results with alpha strings. Characters 0 - 9, as far as I know, always occupy 1 byte. But, as Stew already showed, it is better to use documented UTL_RAW.REVERSE.

                           

                          SY.

                          • 10. Re: question on reverse
                            BrunoVroman

                            Thank you Stew and SY.

                            I thought that Oracle wouldn't present such a function to users if we aren't supposed to use it as it looks quite innocent (I would say in the same family as things like INITCAP); I would expect from Oracle to have a lot of "hidden code" (for example to create revers indexes) that users don't need to see, and that if anyone can run something like REVERSE, it should be documented (and there might be a "byte level" and "char level" versions like LENGTH LENGTHB, or use of a parametrer…)

                            Anyway, nothing critical, just amazing ;-)

                            (I don't remember how I learned that REVERSE existed, and as I wrote if I use it is very rarely and not for serious things)

                            Best regards,

                            Bruno.

                            • 11. Re: question on reverse
                              Solomon Yakobson

                              It can. Length isn't needed even if we sort in ascending order:

                               

                              select  listagg(substr(qid,-level,1)) within group(order by level)

                               

                              SY.

                              • 12. Re: question on reverse
                                BluShadow

                                BrunoVroman wrote:

                                 

                                Thank you Stew and SY.

                                I thought that Oracle wouldn't present such a function to users if we aren't supposed to use it as it looks quite innocent (I would say in the same family as things like INITCAP); I would expect from Oracle to have a lot of "hidden code" (for example to create revers indexes) that users don't need to see, and that if anyone can run something like REVERSE, it should be documented (and there might be a "byte level" and "char level" versions like LENGTH LENGTHB, or use of a parametrer…)

                                Anyway, nothing critical, just amazing ;-)

                                (I don't remember how I learned that REVERSE existed, and as I wrote if I use it is very rarely and not for serious things)

                                Best regards,

                                Bruno.

                                 

                                 

                                Bruno, there are plenty of example of functions that Oracle have created that they use for their own purposes, but have never officially made them public by documenting them.

                                One of the unusual ones is as follows:

                                 

                                Ansi Date literals...

                                 

                                SQL> select date '2019-11-12' from dual;

                                DATE'2019-11-12'
                                --------------------
                                12-NOV-2019 00:00:00

                                 

                                IS documented: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ

                                 

                                But Ansi Time literals...

                                 

                                SQL> select time '15:14:13' from dual;

                                TIME'15:14:13'
                                ------------------------------------------
                                15.14.13.000000000

                                 

                                are also in there, but NOT documented.

                                 

                                 

                                • 13. Re: question on reverse
                                  Solomon Yakobson

                                  It is not just time literals:

                                   

                                  https://community.oracle.com/message/12373734#12373734

                                   

                                  But even in 19C:

                                   

                                  Do not define columns with the following SQL/DS and DB2 data types, because they have no corresponding Oracle data type:

                                  • GRAPHIC
                                  • LONG VARGRAPHIC
                                  • VARGRAPHIC
                                  • TIME

                                  Note that data of type TIME can also be expressed as Oracle datetime data.

                                   

                                  SY.

                                  • 14. Re: question on reverse
                                    Ranagal

                                    Hi SY,

                                     

                                    SY wrote:

                                    select  listagg(substr(qid,-level,1)) within group(order by level)

                                     

                                     

                                    Oh, good observation. Thanks for the explanation.

                                     

                                    Regards,

                                    Ranagal