do the same without using union all

From: Oxnard <shankeypNO_SPAM_at_comcast.net>
Date: Thu, 4 May 2006 06:40:01 -0500
Message-ID: <laqdnY79BreOdcTZnZ2dneKdnZydnZ2d@comcast.com>

9.2.0.7 on solaris 9

CREATE TABLE mytab
(

curr_month varchar2(2),
curr_day   varchar2(2),
curr_year  varchar2(4),

num1 number,
num2 number
);

If I run the SQL:

select

      to_date(curr_year || '-' || curr_month || '-' || curr_day,
'YYYY-MM-DD')

     ,num1 as num
  from mytab
union all
select

      to_date(curr_year || '-' || curr_month || '-' || curr_day,
'YYYY-MM-DD')

     ,num2 as num
  from mytab
order by 1
;

I get the result set I am looking for. I am running the pretty much the same SQL twice. This is not a big deal in a small table. I seem to recall I can do the same thing using one of the analytic functions. Does anyone know how to get this result set without using a union or a union all?

Thank you for your time.
Ox Received on Thu May 04 2006 - 06:40:01 CDT