What ver supports "virtual" tables in FROM clause?

Hi,

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,".

What version of Oracle began support for this sort of querying? I have version 7.3.4.

Thanks,
Phil R Lawrence
prlawrence_at_lehigh.edu

• 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 - 07:54:43 CDT