Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

question on reverse

user1014019Nov 11 2019 — edited Nov 12 2019

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.

Comments

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.

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.

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?

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

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

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

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.

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

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.

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.

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.

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.

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.

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

1 - 14

Post Details

Added on Nov 11 2019
14 comments
577 views