CODEWITHSUNDEEP
sqloracle-databasereplace
Omri
Omri

Reputation: 1646

Replace function in Oracle SQL

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?

Upvotes: 0

Views: 296

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 1

Related Questions