Conditional Defines...

[JaedenRuiner]

I am looking for a way to store a numeric value based on a single row sql computation. I have a table that contains a field representing the count of another table:

insert into count_table
  (tbl1count)
select count(*) from table1;

I do other forms of percentage based analysis using this field:

select id from (
    select
      id,
      col1, row_number() over (order by col1 desc) c1rn
    from table1
 )
 where
   col1 > 0 and c1rn <= (select tbl1count * 0.75 from count_table)

What this does is simply grab the top 75% of the IDs from table1 ordered by the values in col1. THis works fine, but I would like to be able to compute the:

select tbl1count * 0.75 from count_table

and store it in a variable, but 'DEFINE' doesn't work quite the way I want it too. My Idea was this:

DEFINE MyCount1 = (select tbl1count * 0.75 from count_table);

select id from (
  select
    id,
    col1, row_number() over (order by col1 desc) c1rn
  from table1
)
where col1 > 0 and c1rn <= &MyCount1;

I know that define doesn't seem to work like this but is there another way to achieve this result?

 

[Dezsoe Kados]

You could try to use bind variables:

VARIABLE MyCount1 NUMBER;

begin
select tbl1count * 0.75 into :MyCount1 from count_table;
end;
/

select id from (
select
id,
col1, row_number() over (order by col1 desc) c1rn
from table1
)
where col1 > 0 and c1rn

 

[rbrooker]

this will do it for you.

select *
from (select a.id,
             a.col1,
             count(*) over() tblcnt,
             row_number() over(order by col1 desc) rnum
      from table1 a)
where rnum < tblcnt * 0.75