Home » SQL & PL/SQL » SQL & PL/SQL » oracle -rearrange columns values based on value type (12.1.0.2)
oracle -rearrange columns values based on value type [message #680455] Fri, 15 May 2020 07:31 Go to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
I have three oracle tables :
    table_1:
    ID   some_fields
    121      xx
    122      xx
    123      xx
    124      xx
    125      xx

    create table table_1 as
    select 121,'xx' from dual
    union select 122,'xx' from dual
    union select 123,'xx' from dual
    union select 124,'xx' from dual
    union select 125,'xx' from dual


    table_2:
    ID   some_fields
    221      xx
    222      xx
    223      xx
    224      xx
    225      xx

    create table table_2 as
    select 221,'xx' from dual
    union select 222,'xx' from dual
    union select 223,'xx' from dual
    union select 224,'xx' from dual
    union select 225,'xx' from dual

    table3:
    ID    field_1    field_2
    1      121         221
    2      222         125
    3      225         124
    4      123         223
    5      122         224

    create table table_3 as
    select 121,'221' from dual
    union select 222,'125' from dual
    union select 225,'124' from dual
    union select 123,'223' from dual
    union select 122,'224' from dual

I need to re-arrange table_3 to have all ids of table_1 in field_1 and all ids of table_2 in field_2 , knowing that ids in table_1 and table_2 are unique.

    ID    field_1    field_2
    1      121         221
    2      125         222
    3      124         225
    4      123         223
    5      122         224
Noting in reality that these tables contain millions of rows.
I'm thinking of creating a view that accomplish my need but not sure how to proceed.





[Updated on: Fri, 15 May 2020 07:33]

Report message to a moderator

Re: oracle -rearrange columns values based on value type [message #680457 is a reply to message #680455] Fri, 15 May 2020 07:43 Go to previous messageGo to next message
John Watson
Messages: 8293
Registered: January 2010
Location: Global Village
Senior Member
Ghostman, can you post code that actually works? This doesn't:
orclz> create table table_1 as
  2      select 121,'xx' from dual
  3      union select 122,'xx' from dual
  4      union select 123,'xx' from dual
  5      union select 124,'xx' from dual
  6      union select 125,'xx' from dual;
create table table_1 as
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias


orclz>
you can't expect people to debug your test case.
Re: oracle -rearrange columns values based on value type [message #680458 is a reply to message #680455] Fri, 15 May 2020 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2  table_1 as (
  3      select 121 id,'xx' val from dual
  4      union select 122,'xx' from dual
  5      union select 123,'xx' from dual
  6      union select 124,'xx' from dual
  7      union select 125,'xx' from dual
  8  ),
  9  table_2 as (
 10      select 221 id,'xx' val from dual
 11      union select 222,'xx' from dual
 12      union select 223,'xx' from dual
 13      union select 224,'xx' from dual
 14      union select 225,'xx' from dual
 15      union select 226,'xx' from dual
 16  )
 17  select t1.id, t2.id
 18  from (select id, rownum rn from table_1) t1
 19       full outer join
 20       (select id, rownum rn from table_2) t2
 21       on t1.rn=t2.rn
 22  /
        ID         ID
---------- ----------
       121        221
       122        222
       123        223
       124        224
       125        225
                  226

6 rows selected.
Re: oracle -rearrange columns values based on value type [message #680461 is a reply to message #680458] Fri, 15 May 2020 08:00 Go to previous messageGo to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
my goal is not to populate table_3 based on table_1 and table_2.Actually table_3 is already there and contains values from table_1 and table_2 in field_1 and field_2, and I want to re-arrange it as I described in the post.
Re: oracle -rearrange columns values based on value type [message #680462 is a reply to message #680455] Fri, 15 May 2020 08:02 Go to previous messageGo to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
correcting the DDL:

create table table_1 as
    select 121 id ,'xx' some_fields from dual
    union select 122 id,'xx' some_fields from dual
    union select 123 id,'xx' some_fields from dual
    union select 124 id,'xx' some_fields from dual
    union select 125 id,'xx' some_fields from dual

create table table_2 as
    select 221 id,'xx' some_fields from dual
    union select 222 id,'xx 'some_fields from dual
    union select 223 id,'xx' some_fields from dual
    union select 224 id,'xx' some_fields from dual
    union select 225 id,'xx' some_fields from dual

create table table_3 as
    select 1 id, 121 field_1,221 field_2 from dual
    union select 2 id, 222 field_1,125 field_2 from dual
    union select 3 id, 225 field_1,124 field_2 from dual
    union select 4 id, 123 field_1,223 field_2 from dual
    union select 5 id, 122 field_1,224 field_2 from dual
Noting that I don't want to populate table_3 based on data from table_1 and table_2. table_3 is already there and contains values from table_1 and table_2 in field_1 and field_2, and I want to re-arrange it as I described
Re: oracle -rearrange columns values based on value type [message #680464 is a reply to message #680461] Fri, 15 May 2020 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ghostman wrote on Fri, 15 May 2020 15:00
my goal is not to populate table_3 based on table_1 and table_2.Actually table_3 is already there and contains values from table_1 and table_2 in field_1 and field_2, and I want to re-arrange it as I described in the post.
Re: oracle -rearrange columns values based on value type [message #680465 is a reply to message #680462] Fri, 15 May 2020 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
correcting the DDL:

If you want an id before then just add the rn in my query:
SQL> with
  2  table_1 as (
  3      select 121 id,'xx' val from dual
  4      union select 122,'xx' from dual
  5      union select 123,'xx' from dual
  6      union select 124,'xx' from dual
  7      union select 125,'xx' from dual
  8  ),
  9  table_2 as (
 10      select 221 id,'xx' val from dual
 11      union select 222,'xx' from dual
 12      union select 223,'xx' from dual
 13      union select 224,'xx' from dual
 14      union select 225,'xx' from dual
 15      union select 226,'xx' from dual
 16  )
 17  select nvl(t1.rn,t2.rn) id, t1.id field_1, t2.id field_2
 18  from (select id, rownum rn from table_1) t1
 19       full outer join
 20       (select id, rownum rn from table_2) t2
 21       on t1.rn=t2.rn
 22  /
        ID    FIELD_1    FIELD_2
---------- ---------- ----------
         1        121        221
         2        122        222
         3        123        223
         4        124        224
         5        125        225
         6                   226

6 rows selected.
or you could just use "rownum" as the first column.

[Updated on: Fri, 15 May 2020 08:14]

Report message to a moderator

Re: oracle -rearrange columns values based on value type [message #680466 is a reply to message #680465] Fri, 15 May 2020 08:33 Go to previous messageGo to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
Maybe I wasn't clear enough.
table_3 already exist and contain values from table_1 and table_2 in field_1 and field_2.
What I need is to get a result using table_3 data where field_1 only contain table_1 ids and field_2 contains table_2 ids.
so for example for a specific row in table_3 , if field_1 contains an id from table_2 and field_2 contains and id from table_1 , I have to swap the values in field_1 and field_2
Re: oracle -rearrange columns values based on value type [message #680471 is a reply to message #680466] Fri, 15 May 2020 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A question, in table_3, if field_1 contains a value from table_1 does this mean that necessary field_2 contains a value from table_2? And if field_1 contains a value from table_2 does this mean that necessary field_2 contains a value from table_1?
If not what to do with the other value for your result?

Re: oracle -rearrange columns values based on value type [message #680475 is a reply to message #680471] Fri, 15 May 2020 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the hypothesis is correct (table_3 fields are not null and values are in either table_1 or table_2) then you need only one of the 2 later tables (I chose table_1):
SQL> with
  2  table_1 as (
  3      select 121 id,'xx' val from dual
  4      union select 122,'xx' from dual
  5      union select 123,'xx' from dual
  6      union select 124,'xx' from dual
  7      union select 125,'xx' from dual
  8  ),
  9  /*
 10  table_2 as (
 11      select 221 id,'xx' val from dual
 12      union select 222,'xx' from dual
 13      union select 223,'xx' from dual
 14      union select 224,'xx' from dual
 15      union select 225,'xx' from dual
 16  ),
 17  */
 18  table_3 as (
 19      select 121 field_1,'221' field_2 from dual
 20      union select 222,'125' from dual
 21      union select 225,'124' from dual
 22      union select 123,'223' from dual
 23      union select 122,'224' from dual
 24  )
 25  select rownum id,
 26         nvl(t1.id, t3.field_2) field_1,
 27         nvl2(t1.id, t3.field_2, t3.field_1) field_2
 28  from table_3 t3 left outer join table_1 t1
 29       on t1.id = t3.field_1
 30  /
        ID    FIELD_1 FIELD_2
---------- ---------- ----------------------------------
         1        121 221
         2        122 224
         3        123 223
         4        125 222
         5        124 225

5 rows selected.
Re: oracle -rearrange columns values based on value type [message #680543 is a reply to message #680475] Wed, 20 May 2020 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any news on this?

Re: oracle -rearrange columns values based on value type [message #680645 is a reply to message #680543] Thu, 28 May 2020 11:48 Go to previous message
Bill B
Messages: 1966
Registered: December 2004
Senior Member
He doesn't want to respond, put him on the naughty list and no more help for him
Previous Topic: how to display the values with prefix increment?
Next Topic: Dynamic Query into Cursor
Goto Forum:
  


Current Time: Sat Jul 04 01:31:07 CDT 2020