Replace function in Oracle SQL

Question

I'm using oracle SQL, and i have the following query:

select replace(replace('count(distinct <thiscol>) over (partition by <nextcol>) / count(*) over () as <thiscol>_<nextcol>,',
                       '<thiscol>', column_name
                      ), '<nextcol>', lead(column_name) over (order by column_id)
              )
from all_tab_columns atc
where table_name = 'mytable'

The output supposed to be queries such as follow:

select id,
       count(distinct name2) over (partition by name3) / count(*) over (),
       count(distinct name3) over (partition by name4) / count(*) over (),
       . . .
from mytable;

I'm expecting to get instead of:

count(distinct name2) over (partition by name3) / count(*) over ()

this query:

count(distinct name3) over (partition by name2) / count(*) over ()

Anyone can advise how to replace the order of the column values? (<thiscol> and <nextcol>). I tried to replace <thiscol> with <nextcol> but it gave me the same result. I tried many other things with of success.

Anyone?

What output do you get and how that differs from what you want? Please also add an explanation in human language, not SQL, of what you are trying to achieve. — mustaccio
– mustaccio, Commented Dec 3, 2014 at 15:37
Only the value inside over() has changed. The other one didn't. — Omri
– Omri, Commented Dec 3, 2014 at 16:08

Gordon Linoff · Accepted Answer · 2014-12-03 16:13:08Z

1

That is really strange. Instead, let's sort in the reverse order:

select replace(replace('count(distinct <thiscol>) over (partition by <nextcol>) / count(*) over () as <thiscol>_<nextcol>,',
                       '<thiscol>', column_name
                      ), '<nextcol>', lead(column_name) over (order by column_id desc)
              )
from all_tab_columns atc
where table_name = 'mytable';

Note the desc in the sort.

answered Dec 3, 2014 at 16:13

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Omri Over a year ago

@GordonLinoff..You are AMAZING! Thank you so much! I learned a lot from you. Many thanks!

Collectives™ on Stack Overflow

Replace function in Oracle SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related