error in crosstab query

From: Volker Hochstein <v.hochstein_at_sh-home.de>
Date: Sat, 02 Oct 1999 00:14:36 +0200
Message-ID: <37F5324C.92609E60@sh-home.de>

Hello world,

 i ve got a little problem with a query in a view. I want to create a crosstab query direct in oracle. No special tool. In the book sql for smarties I read about cascading select statements. Therefore I write the following view:
CREATE OR REPLACE VIEW viwStatistikMoebelleder ( LieferantNr, PartiePK, APlus, A, B, C, D, E, IV, V, Telquel )

   AS SELECT tblLieferantenNr, tblPartien.PartiePK,

(Select S0.Anzahl

             FROM   tblSortimentPos As S0
             WHERE  S0.PartieFPK = tblPartien.PartiePK AND
                    S0.SortimentFPK = 10012),


            (Select S1.Anzahl


             FROM   tblSortimentPos As S1
             WHERE  S1.PartieFPK = tblPartien.PartiePK AND
                    S1.SortimentFPK = 10013),


            (Select S2.Anzahl


             FROM   tblSortimentPos As S2
             WHERE  S2.PartieFPK = tblPartien.PartiePK AND
                    S2.SortimentFPK = 10014),


            (Select S3.Anzahl


             FROM   tblSortimentPos As S3
             WHERE  S3.PartieFPK = tblPartien.PartiePK AND
                    S3.SortimentFPK = 10015),


            (Select S4.Anzahl


             FROM   tblSortimentPos As S4
             WHERE  S4.PartieFPK = tblPartien.PartiePK AND
                    S4.SortimentFPK = 10016),


            (Select S5.Anzahl


             FROM   tblSortimentPos As S5
             WHERE  S5.PartieFPK = tblPartien.PartiePK AND
                    S5.SortimentFPK = 10017),


            (Select S6.Anzahl


             FROM   tblSortimentPos As S6
             WHERE  S6.PartieFPK = tblPartien.PartiePK AND
                    S6.SortimentFPK = 10018),


            (Select S7.Anzahl


             FROM   tblSortimentPos As S7
             WHERE  S7.PartieFPK = tblPartien.PartiePK AND
                    S7.SortimentFPK = 10019),


            (Select S8.Anzahl


             FROM   tblSortimentPos As S8
             WHERE  S8.PartieFPK = tblPartien.PartiePK AND
                    S8.SortimentFPK = 10020)
     FROM   tblLieferanten, tblPartien
     WHERE  tblLieferanten.LieferantPK = tblPartien.LieferantFK

   WITH READ ONLY But the oracle system /V7.3 does not like it. It stops at the first cascading select with the messaging something is missing. Can anybody help me?

Best regards...
Volker Hochstein
v.hochstein_at_sh-home.de Received on Fri Oct 01 1999 - 17:14:36 CDT