Hello Bhavbhuti Both stored procedures works fine in my tests. The last version of them you can download from:
http://www.mediafire.com/view/ol58gyejul1t43a/CREAR_TABLA_PIVOT.txt http://www.mediafire.com/view/1uebv8uerc5kwpe/ACTUALIZAR_TABLA_PIVOT.txt Check the name of the view. Of course it could to exist. And the names and datatypes of the columns. You can see working examples of pivot tables created and populated with those stored procedures here: http://firebird21.wordpress.com/2013/12/29/creando-y-actualizando-tablas-agregadas-de-uso-general/ http://firebird21.wordpress.com/2013/12/29/otro-ejemplo-de-uso-de-tablas-agregadas/ http://firebird21.wordpress.com/2013/12/30/ventas-mensuales-a-cada-cliente-usando-tablas-agregadas/ So far, the stored procedure CREAR_TABLA_PIVOT just accept as names of the variable columns the characters: A..Z, 0..9, blank space, dot, slash, percent symbol. The SQL error code -104 means: "incorrect syntax in CREATE PROCEDURE" so I think you have a not avowed character in the column CCODE. You can see which is the command writing something like: IN AUTONOMOUS TRANSACTION DO INSERT INTO MyTable (MyColumn) VALUES(:lcCreate); before the line: EXECUTE STATEMENT lcCreate; Execute newly the stored procedure CREAR_TABLA_PIVOT and check the value saved in the table "MyTable" and column "MyColumn" and send me that value, so I can update the stored procedures. Greetings. Walter. On Mon, Dec 30, 2013 at 3:15 AM, Venus Software Operations < [email protected]> wrote: > > > Hi Walter > > I created a view V_SALEINVOICEFOOTER (see below) which returns the exact > data as I had in my example SQL previous email. Then I tried to use the > first procedure but get the following error. Please advise on what I might > be missing. > > Thanks and regards > Bhavbhuti > > > Executing statement... > > Error: *** IBPP::SQLException *** > Context: Statement::Execute( EXECUTE PROCEDURE CREAR_TABLA_PIVOT( > 'MYPIVOT', > 'V_SSALEINVOICEFOOTER', > 'IPID INTEGER', > 'IID INTEGER', > 'CCODE', > 'DOUBLE PRECISION') ) > Message: isc_dsql_execute2 failed > > > SQL Message : -104 > Invalid token > > Engine Code : 335544569 > Engine Message : > Dynamic SQL Error > SQL error code = -104 > Token unknown - line 1, column 60 > ____________________________ > > > Total execution time: 0.031s > > CREATE VIEW V_SSALEINVOICEFOOTER (IPID, IID, CCODE, BAMT) > AS > SELECT r.IPID, m.IID, m.CCODE, r.BAMT > FROM SSALEINVOICEFOOTER r > JOIN MACCOUNTS m > on m.IID = r.IACCOUNTID > ORDER by r.IPID; > > GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE > ON V_SSALEINVOICEFOOTER TO SYSDBA WITH GRANT OPTION; > > > > On 29-12-2013 12:08 pm, W O wrote: > > > Another article of my blog, this one with 2 stored procedures of general > use. One stored procedure create the pivot table and its Primary Key, the > other insert and update data. > > The table or view used as input parameter can have any structure, so > with just those 2 stored procedures you can create 30, 40, 700 or more > distinct pivot tables. > > > http://firebird21.wordpress.com/2013/12/29/creando-y-actualizando-tablas-agregadas-de-uso-general/ > > Greetings. > > Walter. > > > > On Sat, Dec 28, 2013 at 10:25 AM, W O > <[email protected]>wrote: > >> Well, I had added 2 more articles to my blog: >> >> >> http://firebird21.wordpress.com/2013/12/28/creando-tablas-dentro-de-un-stored-procedure-o-de-un-trigger/ >> >> >> http://firebird21.wordpress.com/2013/12/28/creando-tablas-agregadas-e-insertandoles-datos/ >> >> There, I explain a way you can get that you want. >> >> Any doubt, just write me. >> >> Greetings. >> >> Walter. >> >> >> >> >> >> >> >> >> >> On Sat, Dec 28, 2013 at 4:00 AM, Venus Software Operations < >> [email protected]> wrote: >> >>> >>> >>> Thanks Walter. I was hoping to get the columns appear on their own >>> rather than individually predicting them (but if that is not at all >>> possible well). I did have a look at your link and also the ones for >>> Cross-Tab which was more nearer to what I wanted. Please see my present >>> actual query and it's result >>> >>> SELECT r.IPID, m.IID, m.CCODE, r.BAMT >>> FROM SSALEINVOICEFOOTER r >>> JOIN MACCOUNTS m >>> on m.IID = r.IACCOUNTID >>> ORDER by r.IPID >>> >>> 8 12 SALE 25000.000000 >>> 8 64 CENVAT 3000.000000 >>> 8 65 ECESS 60.000000 >>> 8 66 SHCESS 3000.000000 >>> 8 21 31060.000000 >>> 8 60 ST 1242.400000 >>> 8 63 ADDLVAT 310.600000 >>> 8 121 ST 32613.000000 >>> 16 12 SALE 3958.420000 >>> 16 587 EXP 3958.420000 >>> 17 12 SALE 148000.000000 >>> 17 588 148000.000000 >>> 20 12 SALE 27072.000000 >>> 20 64 CENVAT 3248.640000 >>> 20 65 ECESS 64.970000 >>> 20 66 SHCESS 32.490000 >>> >>> What I need to do is convert the above data based on the first column >>> r.iPID being rows. m.cCODE being the column header and r.bAMT being the >>> values for each column. This is what is the end result I was looking >>> towards. Please note ST column for row iPID = 8 is a sum total of two ST >>> cCodes in the same iPID >>> >>> iPID, SALE, CENVAT, ECESS, SHCESS, ST, ADDLVAT, EXP >>> 8, 25000.000000, 3000.000000, 60.000000, 3000.000000, 33855.400000, >>> 310.600000, NULL >>> 16, 3958.420000, NULL, NULL, NULL, NULL, NULL, 3958.420000 >>> 17, 148000.000000, NULL, NULL, NULL, NULL, NULL, NULL >>> 20, 27072.000000, 3248.640000, 64.970000, 32.490000, NULL, NULL, NULL >>> >>> >>> Please advise >>> >>> Thanks and regards >>> Bhavbhuti >>> >> > >
