SQL & PL/SQL

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.

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.

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?

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

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

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

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.

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

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.

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.

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.

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.

Hi SY,

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

Oh, good observation. Thanks for the explanation.

Regards,

Ranagal