question for reverse [message #678113] |
Mon, 11 November 2019 21:51 |
manoj12
Messages: 210 Registered: March 2008 Location: India
|
Senior Member |
|
|
Hi Team,
I wanted your help for the below question
The below is my source query
ID QID QNAME
1 12 MANOJ
2 13 MEERA
3 14 RAM
4 15 RAJ
5 16 KRISHNA
I wanted to write a query having output as shown below
QID QNAME
21 RAJ
31 KRISHNA
41 MANOJ
51 MEERA
61 RAM
Appreciate your help
Regards
|
|
|
|
|
|
Re: question for reverse [message #678261 is a reply to message #678113] |
Thu, 21 November 2019 05:29 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
CREATE TABLE t4 (
id NUMBER(4),
qid NUMBER(4),
qname VARCHAR2(20)
);
ALTER TABLE T4 ADD CONSTRAINT PKID PRIMARY KEY (ID);
INSERT INTO T4 VALUES(1,12,'MANOJ');
INSERT INTO T4 VALUES(2,13,'MEERA');
INSERT INTO T4 VALUES(3,14,'RAM');
INSERT INTO T4 VALUES(4,15,'RAJ');
INSERT INTO T4 VALUES(5,16,'KRISHNA');
SELECT TO_NUMBER(REVERSE(TO_CHAR(QID))) QID,QNAME FROM T4;
[Updated on: Thu, 21 November 2019 05:33] Report message to a moderator
|
|
|
Re: question for reverse [message #678267 is a reply to message #678261] |
Thu, 21 November 2019 07:50 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Do not use undocumented function REVERSE. It is used internally by Oracle for reverse indexes and it reverses bytes, not characters Therefore you'll get unexpected result in multi-byte character sets. And what is most important, as any undocumented function it can change without notice or simple disappear like it happened to WM_CONCAT in 12C. Now, digits are always single-byte characters therefore OP could use documented UTL_RAW.REVERSE (which also reverses bytes):
SELECT TO_NUMBER(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW(QID)))) QID,QNAME FROM T4
/
QID QNAME
---------- --------------------
21 MANOJ
31 MEERA
41 RAM
51 RAJ
61 KRISHNA
SQL>
SY.
|
|
|
Re: question for reverse [message #678279 is a reply to message #678267] |
Fri, 22 November 2019 10:39 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Your requirements make no sense. You have the following list of data
ID QID QNAME
1 12 MANOJ
2 13 MEERA
3 14 RAM
4 15 RAJ
5 16 KRISHNA
and the results that you want are
QID QNAME
21 RAJ
31 KRISHNA
41 MANOJ
51 MEERA
61 RAM
while reversing the QID is easy, you indicate the (for example) 15 is not RAJ, it is MEERA. What are your transform rules to reassign the QNAME
|
|
|