Home » SQL & PL/SQL » SQL & PL/SQL » question for reverse (oracle 11g)
question for reverse [message #678113] Mon, 11 November 2019 21:51 Go to next message
manoj12
Messages: 197
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 #678114 is a reply to message #678113] Tue, 12 November 2019 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

BlackSwan wrote on Fri, 06 February 2015 17:03

You should post follow up so others can benefit from your problem resolution.
You should post follow up because Posting Guidelines say you should be a Good Neighbor.
You should post follow up to thank those who assisted you in resolving your issue.
Re: question for reverse [message #678119 is a reply to message #678114] Tue, 12 November 2019 03:18 Go to previous messageGo to next message
_jum
Messages: 559
Registered: February 2008
Senior Member
Look here.
Re: question for reverse [message #678120 is a reply to message #678113] Tue, 12 November 2019 04:08 Go to previous messageGo to next message
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
Is this a school homework question? If so, you had post the complete question and the SQL you have tried so far.
Re: question for reverse [message #678261 is a reply to message #678113] Thu, 21 November 2019 05:29 Go to previous messageGo to next message
shawaj
Messages: 81
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
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 Go to previous message
Bill B
Messages: 1919
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
Previous Topic: Record Plus Prior Record - Single Row
Next Topic: Break line as per required attached output.
Goto Forum:
  


Current Time: Wed Dec 11 16:48:56 CST 2019