Subject Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Author W O
Post date 2013-12-30T13:36:33Z
Hello Bhavbhuti

Both stored procedures works fine in my tests. The last version of them you can download from:



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:




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 <venussoftop@...> 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.


Greetings.

Walter.



On Sat, Dec 28, 2013 at 10:25 AM, W O <sistemas2000profesional@...> wrote:
Well, I had added 2 more articles to my blog:



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 <venussoftop@...> 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