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!
I have an issue with the table when displaying it with many columns (70), the table columns move from right to left
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
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
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
;
7369 MARTIN
7521 SMITH
7566 MILLER
7654 ADAMS
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_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
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(
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- ?
BrunoVroman wrote:
...
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
select level lvl,
utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(level))) reversed
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;
Stew wrote:
with data(ID,QID,QNAME) as
),
data_with_max(rn,QID,QNAME,max_rn) as
select listagg(substr(qid,length(qid)-level+1,1)) within group(order by level)
nth_value
mod(rn+3,max_rn)+1
over
Can the highlighted one be replaced just with below ?
I mean what might go wrong with my approach ?
Ranagal
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.
Solomon Yakobson wrote:
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.
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)
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)
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:
Note that data of type TIME can also be expressed as Oracle datetime data.
Hi SY,
SY wrote:select listagg(substr(qid,-level,1)) within group(order by level)
SY wrote:
Oh, good observation. Thanks for the explanation.