| Subject | Re: [firebird-support] Re: Full Cross Tab but unknown column headers |
|---|---|
| Author | W O |
| Post date | 2014-01-01T21:48:42Z |
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 <anhanguera@...> 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
>>Yahoo Groups Links
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>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
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>