Re: What ver supports "virtual" tables in FROM clause?

Phil R Lawrence <prlawrence_at_lehigh.edu> wrote:
> Mr. Kats gave me a great solution to a problem involving "virtual" tables
in
> the FROM clause. (See his message below) However, my SQLPlus is
> complaining "ORA-00933: SQL command not properly ended" when it reaches
the
> "as B,".

The problem was the "AS". If I leave that out it works fine.

Thanks,
Phil

> ----- Original Message -----
> From: Kihara kats <Kats.Kihara_at_unisys.co.jp>
> To: <prlawrence_at_lehigh.edu>
> Sent: Tuesday, September 21, 1999 8:04 AM
> Subject: RE: SQL question
>
>
> > Hi,
> >
> > I am not an Oracle expert but I think I can give you an answer.
> >
> > What you need to do is to have two "virtual" tables as follows:
> >
> > _ID_ __TEST_DATE_A_OR_B__
> > 12345678 01/31/1999
> >
> > _ID_ __TEST_DATE_C__
> > 12345678 01/10/1999
> >
> > And join the above two tables. If you are using Oracle whose level
> > is high enough (I do not know exactly what level...), you can do
> > the following query:
> >
> > SELECT A.MAIN_ID,
> > B.TEST_DATE_A_OR_B,
> > C.TEST_DATE_C
> > FROM MAIN A,
> > (SELECT TEST_ID,
> > MAX(TEST_DATE) AS TEST_DATE_A_OR_B
> > FROM TEST
> > WHERE TEST_CODE IN ('A', 'B')
> > GROUP BY TEST_ID
> > ) AS B,
> > (SELECT TEST_ID,
> > MAX(TEST_DATE) AS TEST_DATE_C
> > FROM TEST
> > WHERE TEST_CODE = 'C'
> > GROUP BY TEST_ID
> > ) AS C
> > WHERE A.MAIN_ID = B.TEST_ID
> > AND A.MAIN_ID = C.TEST_ID
> > ;
> >
> > Can you try it? I hope this answers the question. Please let me know the
> > result!
> > Thanks.
> >
> > Regards,
> >
> > Kats Kihara
> > Kats.Kihara_at_unisys.co.jp
> >
> > p.s. You can post my message to the newsgroup, if you want.
> >
> > >Subject:
> > > SQL question
> > > Date:
> > > Mon, 20 Sep 1999 16:12:39 -0400
> > > From:
> > > "Phil R Lawrence" <prlawrence_at_lehigh.edu>
> > > Newsgroups:
> > > comp.databases
> > >
> > >
> > >
> > >
> > >I am using Oracle, but the traffic over at c.d.o.m. seemed more Oracle
> > >specififc than my question is, so I'm posting here.
> > >
> > >2 tables for my simplified problem:
> > >__MAIN__
> > >MAIN_ID NUMBER(8)
> > >
> > >__TEST__
> > >TEST_ID NUMBER(8)
> > >TEST_CODE VARCHAR2(4)
> > >TEST_DATE DATE
> > >
> > >
> > >For my purposes, I am interested in only 3 TEST_CODEs, A, B, and C. I
> need
> > >to select the MAIN_ID, the max(TEST_DATE) wherer TEST_CODE = C, and
> > >whichever max(TEST_DATE) is greater: where TEST_CODE = A or where
> TEST_CODE
> > >= B
> > >
> > >Thus, if table TEST had the following records:
> > >_TEST_ID_ __TEST_CODE__ __TEST_DATE__
> > >12345678 A 01/12/1999
> > >12345678 A 01/18/1999
> > >12345678 B 01/01/1999
> > >12345678 B 01/31/1999
> > >12345678 C 01/08/1999
> > >12345678 C 01/10/1999
> > >
> > >I would need the following returned from my SQL:
> > >___ID___ __MAX_DATE_A_OR_B__ __MAX_DATE_C__
> > >12345678 01/31/1999 01/10/1999
> > >
> > >
> > >Can anyone help me with this?
> > >
> > >TIA,
> > >Phil R Lawrence
> > >prlawrence_at_lehigh.edu
> > >
> > <-----snip----->
> >
>
>
Received on Tue Sep 21 1999 - 08:08:44 CDT