sql question

From: Oxnard <shankeypNO_SPAM_at_comcast.net>
Date: Tue, 10 Jan 2006 16:06:15 -0600
Message-ID: <-M2dnVzTkvFGslneRVn-ow@comcast.com>

Oracle 9.2.0.5

I want to put a default value of 0 for number which may or maynot be in a table. I have found that if I do:

select num, the_count from
(select num, count(*) as the_count
from

                   (select numa as num from mytab
                     union all
                    select numb as num  from mytab
                     union all
                    select numc as num from mytab
                      union all
                    select numd as num from mytab
                      union all
                    select nume as num from mytab)

group by num)
union
  (select to_number(case when 1 = 1 then 1 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 2 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 3 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 4 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 5 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 6 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 7 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 8 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 9 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 10 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 11 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 12 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 13 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 14 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 15 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 16 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 17 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 18 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 19 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 20 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 21 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 22 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 23 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 24 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 25 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 26 end) as num, 0 as the_count from dual
  union
select to_number(case when 1 = 1 then 27 end) as num, 0 as the_count from dual)
order by 2,1;

I do get the result set I want. As 0 is the default for the_count. Also even if there is not say a num of 26 in mytab I want to show that with a 0 count in the result set.

I would like to know if there is a 'less wordy' way to write my default setter? I will be using this in a package and I do know the potenial max of num in mytab in advance so something like

theMaxNum integer := 25;

would be real good

thank you for your time. Received on Tue Jan 10 2006 - 16:06:15 CST