Forum Stats

  • 3,758,242 Users
  • 2,251,358 Discussions
  • 7,870,124 Comments

Discussions

question on reverse

user1014019
user1014019 Member Posts: 238 Bronze Badge
edited Nov 12, 2019 10:59AM in SQL & PL/SQL

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.

Tagged:
AndrewSayerFrank KulashRanagalDejan T.BrunoVromanL. FernigriniStew Ashton
«1

Answers

  • mathguy
    mathguy Member Posts: 10,080 Blue Diamond
    edited Nov 12, 2019 12:50AM

    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.enamefrom   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.

    AndrewSayer
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Nov 12, 2019 1:49AM
    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.

    Frank Kulash
  • BEDE
    BEDE Oracle Developer Member Posts: 2,283 Gold Trophy
    edited Nov 12, 2019 3:55AM

    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?

    Frank Kulash
  • Stew Ashton
    Stew Ashton Member Posts: 2,861 Gold Trophy
    edited Nov 12, 2019 10:59AM

    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

    Frank KulashRanagalDejan T.
  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Nov 12, 2019 5:48AM

    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
    Stew Ashton Member Posts: 2,861 Gold Trophy
    edited Nov 12, 2019 6:15AM
    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

    BrunoVromanRanagal
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond
    edited Nov 12, 2019 6:28AM

    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.

    BrunoVroman
  • Ranagal
    Ranagal Member Posts: 635 Bronze Badge
    edited Nov 12, 2019 6:34AM

    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

    Stew Ashton
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond
    edited Nov 12, 2019 6:34AM
    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
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Nov 12, 2019 6:37AM

    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.