Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Well, it is not complicated at all, I was waiting for somebody writing a better technique but the days go on and that not happen then I show you a link to a page where the technique is described, with two examples. The page is in Spanish but you can use some translator (Google has one). And of course, if you have any question, just tell me, here or in the page: http://firebird21.wordpress.com/2013/11/24/convirtiendo-filas-en-columnas/ One drawback of the technique is that you need to know in advance the exact number of columns or at least the maximum number of columns. Greetings. Walter. On Fri, Dec 27, 2013 at 7:28 AM, Svein Erling Tysvær < svein.erling.tysv...@kreftregisteret.no> wrote: > > > >Hi all > > > >Merry Christmas everyone! > > > >I have had to revisit this issue. I have a simplified table > >Color, Amount > >Red, 123 > >Green, 234 > >Blue, 345 > > > >Previously, I have been advised of CTEs and CASE before wherein I was > able to get the result but for it I had to assume that such and such Colors > names existed. > >But the thing is that the Color value is not under control and is not a > fixed list, so one user could have a > >Light Green, 456 > >and another user could define the same as > >Pale Green, 567 > >I want the colors to become the header in the output and the Amount > underneath,something like > > > >Red, Green, Blue, Light Green, Pale Green > >123, 234, 345, 456, 567 > > > >Please advise > > Hi Bhavbhuti! > > SQL - at least the way Firebird implements it - is basically lousy at > turning rows into columns, this is work more suitable for something like > Excel (PivotTable), FastReport or some other tools/components. I think it > is doable in Firebird using EXECUTE STATEMENT inside EXECUTE BLOCK, but it > sounds more complicated than the alternatives mentioned above. I think one > of the reasons for this being complicated to do in SQL, is that you > normally need to know the number and type of columns in advance. > > Sorry, > Set > > >
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
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 < venussof...@gmail.com> 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 > > 812SALE 25000.00 > 864CENVAT3000.00 > 865ECESS 60.00 > 866SHCESS3000.00 > 821 31060.00 > 860ST1242.40 > 863ADDLVAT 310.60 > 8121ST32613.00 > 1612SALE 3958.42 > 16587EXP 3958.42 > 1712SALE 148000.00 > 17588 148000.00 > 2012SALE 27072.00 > 2064CENVAT3248.64 > 2065ECESS 64.97 > 2066SHCESS32.49 > > 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.00, 3000.00, 60.00, 3000.00, 33855.40, > 310.60, NULL > 16, 3958.42, NULL, NULL, NULL, NULL, NULL, 3958.42 > 17, 148000.00, NULL, NULL, NULL, NULL, NULL, NULL > 20, 27072.00, 3248.64, 64.97, 32.49, NULL, NULL, NULL > > > Please advise > > Thanks and regards > Bhavbhuti > > > > > > On 28-12-2013 02:26 am, W O wrote: > > > Well, it is not complicated at all, I was waiting for somebody writing a > better technique but the days go on and that not happen then I show you a > link to a page where the technique is described, with two examples. The > page is in Spanish but you can use some translator (Google has one). And of > course, if you have any question, just tell me, here or in the page: > > > http://firebird21.wordpress.com/2013/11/24/convirtiendo-filas-en-columnas/ > > One drawback of the technique is that you need to know in advance the > exact number of columns or at least the maximum number of columns. > > Greetings. > > Walter. > > > > > > On Fri, Dec 27, 2013 at 7:28 AM, Svein Erling Tysvær < > svein.erling.tysv...@kreftregisteret.no> wrote: > >> >> >Hi all >> > >> >Merry Christmas everyone! >> > >> >I have had to revisit this issue. I have a simplified table >> >Color, Amount >> >Red, 123 >> >Green, 234 >> >Blue, 345 >> > >> >Previously, I have been advised of CTEs and CASE before wherein I was >> able to get the result but for it I had to assume that such and such Colors >> names existed. >> >But the thing is that the Color value is not under control and is not a >> fixed list, so one user could have a >> >Light Green, 456 >> >and another user could define the same as >> >Pale Green, 567 >> >I want the colors to become the header in the output and the Amount >> underneath,something like >> > >> >Red, Green, Blue, Light Green, Pale Green >> >123, 234, 345, 456, 567 >> > >> >Please advise >> >> Hi Bhavbhuti! >> >> SQL - at least the way Firebird implements it - is basically lousy at >> turning rows into columns, this is work more suitable for something like >> Excel (PivotTable), FastReport or some other tools/components. I think it >> is doable in Firebird using EXECUTE STATEMENT inside EXECUTE BLOCK, but it >> sounds more complicated than the alternatives mentioned above. I think one >> of the reasons for this being complicated to do in SQL, is that you >> normally need to know the number and type of columns in advance. >> >> Sorry, >> Set >> > > > -- > > > Thanking you. > > Yours Faithfully, > For Venus Software Operations > > Mr. Bhavbhuti Nathwani > ___ > Softwares for Indian Businesses at: http://www.venussoftop.com > venussoftop@gmail.comvenussof...@mail.ru > _
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
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 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 < > venussof...@gmail.com> 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 >> >> 812SALE 25000.00 >> 864CENVAT3000.00 >> 865ECESS 60.00 >> 866SHCESS3000.00 >> 821 31060.00 >> 860ST1242.40 >> 863ADDLVAT 310.60 >> 8121ST32613.00 >> 1612SALE 3958.42 >> 16587EXP 3958.42 >> 1712SALE 148000.00 >> 17588 148000.00 >> 2012SALE 27072.00 >> 2064CENVAT3248.64 >> 2065ECESS 64.97 >> 2066SHCESS32.49 >> >> 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.00, 3000.00, 60.00, 3000.00, 33855.40, >> 310.60, NULL >> 16, 3958.42, NULL, NULL, NULL, NULL, NULL, 3958.42 >> 17, 148000.00, NULL, NULL, NULL, NULL, NULL, NULL >> 20, 27072.00, 3248.64, 64.97, 32.49, NULL, NULL, NULL >> >> >> Please advise >> >> Thanks and regards >> Bhavbhuti >> >> >> >> >> >> On 28-12-2013 02:26 am, W O wrote: >> >> >> Well, it is not complicated at all, I was waiting for somebody writing a >> better technique but the days go on and that not happen then I show you a >> link to a page where the technique is described, with two examples. The >> page is in Spanish but you can use some translator (Google has one). And of >> course, if you have any question, just tell me, here or in the page: >> >> >> http://firebird21.wordpress.com/2013/11/24/convirtiendo-filas-en-columnas/ >> >> One drawback of the technique is that you need to know in advance the >> exact number of columns or at least the maximum number of columns. >> >> Greetings. >> >> Walter. >> >> >> >> >> >> On Fri, Dec 27, 2013 at 7:28 AM, Svein Erling Tysvær < >> svein.erling.tysv...@kreftregisteret.no> wrote: >> >>> >>> >Hi all >>> > >>> >Merry Christmas everyone! >>> > >>> >I have had to revisit this issue. I have a simplified table >>> >Color, Amount >>> >Red, 123 >>> >Green, 234 >>> >Blue, 345 >>> > >>> >Previously, I have been advised of CTEs and CASE before wherein I was >>> able to get the result but for it I had to assume that such and such Colors >>> names existed. >>> >But the thing is that the Color value is not under control and is not a >>> fixed list, so one user could have a >>> >Light Green, 456 >>> >and another user could define the same as >>> >Pale Green, 567 >>> >I want the colors to become the header in the output and the Amount >>> underneath,something like >>> > >>> >Red, Green, Blue, Light Green, Pale Green >>> >123, 234, 345, 456, 567 >>> > >>> >Please advise >>> >>> Hi Bhavbhuti! >>> >>> SQL - at least the way Firebird implements it - is basically lousy at >>> turning rows into columns, this is work more suitable for something like >>> Excel (PivotTable), FastRep
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
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 < venussof...@gmail.com> 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 > 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 < >> venussof...@gmail.com> 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 >>> >>> 812SALE 25000.00 >>> 864CENVAT3000.00 >>> 865ECESS 60.00 >>> 866SHCESS3000.00 >>> 821 31060.00 >>> 860ST1242.40 >>> 863ADDLVAT 310.60 >>> 8121ST32613.00 >>> 1612SALE 3958.42 >>> 16587EXP 3958.42 >>> 1712SALE 148000.00 >>> 1758
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Thanks Walter. I updated both the SPs with the ones you provided they
commited well.
So I dropped the original CREAR_... SP and recopied your original with
the extra IN AUTONOMOUS line added to it. But maybe my version of
FireBird is wrong I get an error for the IN line and AUTONOMOUS word is
not highlighted in FlameRobin so it might be a new reserved word which
my version does not have. I am using FB 2.1.4.18393. I have also
create the MYTABLE and it's field CREATINGCODE of VARCHAR(2048)
I have also tried to remove the IN line and kept the INSERT line then
nothing is stored in MYTABLE. I have also tried to remove the
AUTONOMOUS word but kept the IN and INSERT line then too no luck.
Please advise
Thanks and regards
Bhavbhuti
Starting transaction...
Preparing statement:
CREATE PROCEDURE CREAR_TABLA_PIVOT(
TCNOMBRETABLA VARCHAR(28),
TCVISTA VARCHAR(28),
...
lcCreate = Left(lcCreate, Char_Length(lcCreate) - 2);
lcCreate = lcCreate || ');';
IN AUTONOMOUS TRANSACTION DO
INSERT INTO MYTABLE (CREATINGCODE) VALUES(:lcCreate);
EXECUTE STATEMENT lcCreate;
-- Segundo, le agregamos una Primary Key
EXECUTE STATEMENT
'ALTER TABLE ' || tcNombreTabla ||
' ADD CONSTRAINT PK_' || tcNombreTabla ||
' PRIMARY KEY (' || Left(:tcPrimeraColumnaCabecera, Position(' ',
:tcPrimeraColumnaCabecera)) || ')';
END
Error: *** IBPP::SQLException ***
Context: Statement::Prepare(
CREATE PROCEDURE CREAR_TABLA_PIVOT(
TCNOMBRETABLA VARCHAR(28),
TCVISTA VARCHAR(28),
...
...
lcCreate = lcCreate || ');';
IN AUTONOMOUS TRANSACTION DO
INSERT INTO MYTABLE (CREATINGCODE) VALUES(:lcCreate);
EXECUTE STATEMENT lcCreate;
-- Segundo, le agregamos una Primary Key
EXECUTE STATEMENT
'ALTER TABLE ' || tcNombreTabla ||
' ADD CONSTRAINT PK_' || tcNombreTabla ||
' PRIMARY KEY (' || Left(:tcPrimeraColumnaCabecera, Position(' ',
:tcPrimeraColumnaCabecera)) || ')';
END )
Message: isc_dsql_prepare failed
SQL Message : -104
Invalid token
Engine Code: 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 46, column 1
IN
Total execution time: 0.016s
On 30-12-2013 07:06 pm, W O wrote:
> IN AUTONOMOUS TRANSACTION DO
>INSERT INTO MyTable (MyColumn) VALUES(:lcCreate);
--
Thanking you.
Yours Faithfully,
For Venus Software Operations
Mr. Bhavbhuti Nathwani
___
Softwares for Indian Businesses at: http://www.venussoftop.com
venussof...@gmail.com
venussof...@mail.ru
___
Please note: We reserve complete rights for policy changes in the future and
the same will be applicable immediately as and when made. Attachments may get
corrupted before reaching you, in such a situation please let us know and we
will resend you the same at the earliest. We do not take any responsibility
for data loss of any type and kind. Data safety remains the sole the
responsibility of the users of our softwares.
___
Internet email confidentiality:
This message may contain information that may be privileged or confidential.
If you are not the addressee nor are you responsible for the delivery of the
message to the addressee indicated in this email, then you may not copy or
deliver this email to anyone and you should notify the sender by reply email
and then destroy this message.
Please reply email immediately to this message with REMOVE in the subject, if
you or your employer do not consent to email of this kind.
Opinions, conclusions and other information in this message that do not relate
to the official business of my firm shall be understood as neither given nor
endorsed by my company.
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
The lines IN AUTONOMOUS TRANSACTION DO
and
INSERT INTO...
are for knowing the errors, you can delete both of them now and try again.
Your version of Firebird needs to compile the following commands and
funtions:
CHAR_LENGTH()
IF() THEN
LEFT()
POSITION()
REPLACE()
WHILE() DO
EXECUTE STATEMENT
FOR EXECUTE STATEMENT
With Firebird 2.5.2 the stored procedures works fine, I dont know with
which previous version they dont work.
Greetings.
Walter.
On Mon, Dec 30, 2013 at 10:13 AM, Venus Software Operations <
venussof...@gmail.com> wrote:
> Thanks Walter. I updated both the SPs with the ones you provided they
> commited well.
>
> So I dropped the original CREAR_... SP and recopied your original with
> the extra IN AUTONOMOUS line added to it. But maybe my version of
> FireBird is wrong I get an error for the IN line and AUTONOMOUS word is
> not highlighted in FlameRobin so it might be a new reserved word which
> my version does not have. I am using FB 2.1.4.18393. I have also
> create the MYTABLE and it's field CREATINGCODE of VARCHAR(2048)
>
> I have also tried to remove the IN line and kept the INSERT line then
> nothing is stored in MYTABLE. I have also tried to remove the
> AUTONOMOUS word but kept the IN and INSERT line then too no luck.
>
> Please advise
>
> Thanks and regards
> Bhavbhuti
>
> Starting transaction...
> Preparing statement:
>
> CREATE PROCEDURE CREAR_TABLA_PIVOT(
>TCNOMBRETABLA VARCHAR(28),
>TCVISTA VARCHAR(28),
>
> ...
> lcCreate = Left(lcCreate, Char_Length(lcCreate) - 2);
>
> lcCreate = lcCreate || ');';
>
> IN AUTONOMOUS TRANSACTION DO
> INSERT INTO MYTABLE (CREATINGCODE) VALUES(:lcCreate);
>
> EXECUTE STATEMENT lcCreate;
>
> -- Segundo, le agregamos una Primary Key
>
> EXECUTE STATEMENT
>'ALTER TABLE ' || tcNombreTabla ||
>' ADD CONSTRAINT PK_' || tcNombreTabla ||
>' PRIMARY KEY (' || Left(:tcPrimeraColumnaCabecera, Position(' ',
> :tcPrimeraColumnaCabecera)) || ')';
>
> END
> Error: *** IBPP::SQLException ***
> Context: Statement::Prepare(
>
> CREATE PROCEDURE CREAR_TABLA_PIVOT(
>TCNOMBRETABLA VARCHAR(28),
>TCVISTA VARCHAR(28),
> ...
> ...
> lcCreate = lcCreate || ');';
>
> IN AUTONOMOUS TRANSACTION DO
> INSERT INTO MYTABLE (CREATINGCODE) VALUES(:lcCreate);
>
> EXECUTE STATEMENT lcCreate;
>
> -- Segundo, le agregamos una Primary Key
>
> EXECUTE STATEMENT
>'ALTER TABLE ' || tcNombreTabla ||
>' ADD CONSTRAINT PK_' || tcNombreTabla ||
>' PRIMARY KEY (' || Left(:tcPrimeraColumnaCabecera, Position(' ',
> :tcPrimeraColumnaCabecera)) || ')';
>
> END )
> Message: isc_dsql_prepare failed
>
> SQL Message : -104
> Invalid token
>
> Engine Code: 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 46, column 1
> IN
>
>
> Total execution time: 0.016s
>
>
>
> On 30-12-2013 07:06 pm, W O wrote:
> > IN AUTONOMOUS TRANSACTION DO
> >INSERT INTO MyTable (MyColumn) VALUES(:lcCreate);
>
> --
>
>
> Thanking you.
>
> Yours Faithfully,
> For Venus Software Operations
>
> Mr. Bhavbhuti Nathwani
> ___
> Softwares for Indian Businesses at: http://www.venussoftop.com
>
> venussof...@gmail.com
> venussof...@mail.ru
> ___
>
> Please note: We reserve complete rights for policy changes in the future
> and the same will be applicable immediately as and when made. Attachments
> may get corrupted before reaching you, in such a situation please let us
> know and we will resend you the same at the earliest. We do not take any
> responsibility for data loss of any type and kind. Data safety remains the
> sole the responsibility of the users of our softwares.
> ___
>
> Internet email confidentiality:
>
> This message may contain information that may be privileged or
> confidential. If you are not the addressee nor are you responsible for the
> delivery of the message to the addressee indicated in this email, then you
> may not copy or deliver this email to anyone and you should notify the
> sender by reply email and then destroy this message.
>
> Please reply email immediately to this message with REMOVE in the subject,
> if you or your employer do not consent to email of this kind.
>
> Opinions, conclusions and other information in this message that do not
> relate to the official business of my firm shall be understood as neither
> given nor endorsed by my company.
>
>
>
>
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo Groups Links
>
>
>
>
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
I agree: more complicated, a lot. And still less dynamic and powerfull.
--
Em sáb, 28 de dez de 2013 18:02 MSK Svein Erling Tysvær escreveu:
>Hi Bhavbhuti!
>
>Let's split this into two parts.
>
>Running
>
>EXECUTE BLOCK returns (Mystatement varchar(4096)) AS
>DECLARE VARIABLE S VARCHAR(256);
>DECLARE VARIABLE I INTEGER;
>DECLARE VARIABLE S2 VARCHAR(256);
>DECLARE VARIABLE SIIF VARCHAR(1024);
>DECLARE VARIABLE SJOIN VARCHAR(1024);
>BEGIN
> S='';
> S2='';
> SIIF='';
> SJOIN='';
> I=1;
> FOR SELECT 'LEFT JOIN MACCOUNTS MA'||:I||' ON MA'||:I||'.CCODE = '''||CCODE||
>''' AND MA'||:I||'.IID = r.IACCOUNTID ',
> 'SUM(IIF(MA'||:I||'.IID IS NOT NULL, r.BAMT, 0))'
> FROM MACCOUNTS MA1
> WHERE MA1.CCODE > ''
>AND NOT EXISTS(SELECT * FROM MACCOUNTS MA2
> WHERE MA1.CCODE = MA2.CCODE
> AND MA1.IID > MA2.IID) INTO :S, S2 DO
> BEGIN
>SJOIN = SJOIN||S;
>SIIF = SIIF||', '||S2;
>I=I+1;
> END
>
> FOR WITH TMP(FIELD_NAMES) AS
>(SELECT LIST(CCODE) FROM MACCOUNTS MA1
>WHERE MA1.CCODE > ''
> AND NOT EXISTS(SELECT * FROM MACCOUNTS MA2
> WHERE MA1.CCODE = MA2.CCODE
> AND MA1.IID > MA2.IID))
>SELECT 'WITH TMP(IPID,'||FIELD_NAMES||') AS (SELECT r.IPID'||:SIIF||
> 'FROM SSALEINVOICEFOOTER r ' || :SJOIN||' GROUP BY 1) SELECT *
> FROM TMP'
>FROM TMP INTO :MyStatement DO
> SUSPEND;
>END;
>
>will return a statement that, when run, will return almost the result you want
>('almost' being that it returns 0 and not NULL for columns not existing and
>that you may have to do some modifications if there may exist named rows in
>MACCOUNTS that doesn't exist in SSALEINVOICEFOOTER and hence, shouldn't be a
>column).
>
>I still think this is far more complicated than doing such conversions using
>PivotTables in Excel or similar.
>
>HTH,
>Set
>
>
>
>++
>
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++
>Yahoo Groups Links
>
>
>
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
More complicated, no.
With just some stored procedures you can have everything you need.
Yes, they requiere to be written but after that the use is very simple and
can be used millions of times, without problem.
Less dynamic and powerful, yes.
But you can always improve the stored procedures as you need or want.
You can use Excel, of course, but that is an alien program, outside of
Firebird and in some cases you can not force your client to buy Excel. An
internal solution, by the other hand, always works fine and can be improved
at your taste.
Greetings.
Walter.
On Wed, Jan 1, 2014 at 9:36 AM, Roberto wrote:
>
>
>
> I agree: more complicated, a lot. And still less dynamic and powerfull.
>
> --
> Em sáb, 28 de dez de 2013 18:02 MSK Svein Erling Tysvær escreveu:
>
>
> >Hi Bhavbhuti!
> >
> >Let's split this into two parts.
> >
> >Running
> >
> >EXECUTE BLOCK returns (Mystatement varchar(4096)) AS
> >DECLARE VARIABLE S VARCHAR(256);
> >DECLARE VARIABLE I INTEGER;
> >DECLARE VARIABLE S2 VARCHAR(256);
> >DECLARE VARIABLE SIIF VARCHAR(1024);
> >DECLARE VARIABLE SJOIN VARCHAR(1024);
> >BEGIN
> > S='';
> > S2='';
> > SIIF='';
> > SJOIN='';
> > I=1;
> > FOR SELECT 'LEFT JOIN MACCOUNTS MA'||:I||' ON MA'||:I||'.CCODE =
> '''||CCODE||
> > ''' AND MA'||:I||'.IID = r.IACCOUNTID ', 'SUM(IIF(MA'||:I||'.IID IS NOT
> NULL, r.BAMT, 0))'
> > FROM MACCOUNTS MA1
> > WHERE MA1.CCODE > ''
> > AND NOT EXISTS(SELECT * FROM MACCOUNTS MA2
> > WHERE MA1.CCODE = MA2.CCODE
> > AND MA1.IID > MA2.IID) INTO :S, S2 DO
> > BEGIN
> > SJOIN = SJOIN||S;
> > SIIF = SIIF||', '||S2;
> > I=I+1;
> > END
> >
> > FOR WITH TMP(FIELD_NAMES) AS
> > (SELECT LIST(CCODE) FROM MACCOUNTS MA1
> > WHERE MA1.CCODE > ''
> > AND NOT EXISTS(SELECT * FROM MACCOUNTS MA2
> > WHERE MA1.CCODE = MA2.CCODE
> > AND MA1.IID > MA2.IID))
> > SELECT 'WITH TMP(IPID,'||FIELD_NAMES||') AS (SELECT r.IPID'||:SIIF||
> > 'FROM SSALEINVOICEFOOTER r ' || :SJOIN||' GROUP BY 1) SELECT * FROM TMP'
> > FROM TMP INTO :MyStatement DO
> > SUSPEND;
> >END;
> >
> >will return a statement that, when run, will return almost the result you
> want ('almost' being that it returns 0 and not NULL for columns not
> existing and that you may have to do some modifications if there may exist
> named rows in MACCOUNTS that doesn't exist in SSALEINVOICEFOOTER and hence,
> shouldn't be a column).
> >
> >I still think this is far more complicated than doing such conversions
> using PivotTables in Excel or similar.
> >
> >HTH,
> >Set
> >
> >
>
> >
> >++
> >
> >Visit http://www.firebirdsql.org and click the Resources item
> >on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> >Also search the knowledgebases at http://www.ibphoenix.com
> >
> >++
> >Yahoo Groups Links
> >
> >
> >
>
>
>
Re: [firebird-support] Re: Full Cross Tab but unknown column headers
Re: [firebird-support] RE: Full Cross Tab but unknown column headers
Do you have the SET TERM? Greetings. Walter. On Sat, Jan 4, 2014 at 2:41 AM, wrote: > > > Hi Walter > > Okay I upgraded to FB 2.5.2.26540. I have successfully created the SPs > Parser and Crear_Tabla_Pivot but when I try to create the SP called > ACTUALIZAR_TABLA_PIVOT (I tried the one from your web page as well as the > one you sent me as a download) I get the error: > > Executing statement... > > Error: *** IBPP::SQLException *** > Context: Statement::Execute( > > CREATE PROCEDURE ACTUALIZAR_TABLA_PIVOT( >tcTablaPivot VARCHAR( 28), >tcVista VARCHAR( 28), >tcPr... > ... > ...STATEMENT lcActualizar; > END >END > > END ) > Message: isc_dsql_execute2 failed > > SQL Message : -607 > This operation is not defined for system tables. > > Engine Code: 335544351 > Engine Message : > unsuccessful metadata update > STORE RDB$PROCEDURES failed > Malformed string > > > Total execution time: 0.015s > > > Surprisingly I was able to create this SP well in FB 2.1.4! Please advise. > > Thanks and regards > Bhavbhuti > > >
RE: [firebird-support] Re: Full Cross Tab but unknown column headers
By the way, google's query language has a PIVOT syntax: https://developers.google.com/chart/interactive/docs/querylanguage#Pivot Eg. select sum(salary) pivot dept Would be nice if Firebird could do the same, I understand you couldn't use it inside a stored procedure, where your columns need to be known up front, but your could use it to query your selectable stored procedure, which would be very nice ;-)
