Re: sql question

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:43c4a3cc$0$22221$626a14ce_at_news.free.fr...
>
> "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
>
>

Thank you Sir Received on Wed Jan 11 2006 - 21:26:58 CST