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
>
>
>
