views and aliases column question

From: Eric Bycer <ebycer_at_mediaone.net>
Date: Fri, 29 Sep 2000 01:55:07 GMT
Message-ID: <B5F96FC0.BB%ebycer@mediaone.net>

Hi,

You guys were a great help to me last time, so I figured I'd try again. This is a bit long, so please bear with me.

I am trying to get as far away from creating temp tables as I can (so I don't have to trouble the DBA every time I have to set up shop on a new server and get a tablespace set up, but I digress), as well as decrease running time from twenty/thirty minutes to about 45 seconds by using views.

What I am trying to do is this:
I need to keep track of a series of trouble reports (TRs) and when they were set to certain statuses. Because some statuses get skipped, I have to be a little creative. It dies work with temp tables, but it takes some 30 or so temp tables, so you can imagine the hassle.

Here is the code that I have written for Oracle 7.1:

create or replace view data_view as
select distinct a.tr, a.product, a.priorityfinal, a.class, a.status,

       b.statusdate as open_date, b.statusdate as close_date,
       b.statusdate as compl_date, b.statusdate as new_date,
       b.statusdate as ui_date, b.statusdate as analyzed_date

from tr a, statushistory b
where a.tr = b.tr

      and ((a.defecttype in ('LOGIC'...'COMPUTATIONAL')) or
           (a.category = 'SOFTWARE REQUIREMENTS ANALYSIS'
               and a.defecttype = 'DOCUMENTATION'))
      and a.status in ('APPROVED','IMPLEMENTED','REWORK','INCORPORATED',
                       'COMPLETED')
      and a.class in ('TR','IR-SW')
      and open_date = (select min(statusdate)
                       from statushistory b
                       where a.tr = b.tr
                       and b.status in ('APPROVED',....))
      and close_date = (select min(statusdate)
                       from statushistory b
                       where a.tr = b.tr
                       and b.status in ('INCORPORATED',....))
      and comp_date = (select min(statusdate)
                       from statushistory b
                       where a.tr = b.tr
                       and b.status = 'COMPLETED')
      and new_date = (select min(statusdate)
                       from statushistory b
                       where a.tr = b.tr
                       and b.status = 'NEW')
      and ui_date = (select min(statusdate)
                       from statushistory b
                       where a.tr = b.tr
                       and b.status = 'UNDER INVESTIGATION')
      and analyzed_date = (select min(statusdate)
                       from statushistory b
                       where a.tr = b.tr
                       and b.status = 'ANALYZED');

commit;

I get an error saying "unknown column" for each of the date subselects, and I can't figure out why. The dates come from the statushistory table, so why am I getting an error on aliased columns? Am I trying to do something that is patently illegal in views? Should I use more than one view? Will I be forced to use tables?

Any help would be greatly appreciated. If this isn't clear, I will be happy to try to further clarify.

Thank you very much,
Eric Bycer Received on Thu Sep 28 2000 - 20:55:07 CDT