question on reverse

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.

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_qnamefrom 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 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))) reversedfrom dualconnect 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

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.