Re: sql question

"Oxnard" <shankeypNO_SPAM_at_comcast.net> a écrit dans le message de news: -M2dnVzTkvFGslneRVn-ow_at_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.
|
|

The second part of you query (all the select from dual) is just:

SQL> select rownum num, 0 the_count from dual connect by level <= 27;

       NUM THE_COUNT
---------- ----------

         1          0
         2          0
         3          0
         4          0
         5          0
         6          0
         7          0
         8          0
         9          0
        10          0
        11          0
        12          0
        13          0
        14          0
        15          0
        16          0
        17          0
        18          0
        19          0
        20          0
        21          0
        22          0
        23          0
        24          0
        25          0
        26          0
        27          0

27 rows selected.

Regards
Michel Cadot Received on Wed Jan 11 2006 - 00:21:00 CST