| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: views and aliases column question
Eric,
You can't use aliases for columns in the where clause: open_date, close_date... In this case Oracle will raise the unknown column error. I checked it. Use column names instead of aliases in the where clause.
Alex,
Sorry for correcting, but there is no difference
whether you add 'as' or not to the select list.
I checked it.
-- Regards, Zbigniew Sliwa Oracle Programmer Poland email: zibi_at_at_yahoo.com Alexander Medvedev wrote:Received on Sat Sep 30 2000 - 01:48:23 CDT
>
> See answer below.
>
> Eric Bycer wrote:
>
> > 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
>
> I guess only you need is to check your syntax. Why do you add "as" between real
> column name and
> aliase you want to use? As a result, Oracle understand your "as" as alias for
> this column and
> do not know what to do with the rest up to comma. Thus you get unknown column
> error message.
>
> To solve this you need to remove each "as" from the select list clause.
>
> Regards Alexander Medvedev
 |
 |