Da, samo nisam imao vremena da posaljem, dosta sam zauzet ovih dana.
Dole je na brzinu sklepano resenje ali prvo da vidimo sta je problem. Recimo da imamo query:
Code:
SELECT sum(purchase) as Potroseno, sum (depozit) as Zaradjeno, sum (taksa) as Porez FROM Transakcije
rezultat ce biti:
Code:
Potroseno Zaradjeno Porez
---------------------------------
1234.56 43.21 77.5
Ovo ne mozemo da koristimo u izvestajima u Birt dizajneru jer nam treba da jednu kolonu stavimo za X osu a drugu za Y da bismo mogli npr. da na X osi imamo Potroseno i Zaradjeno a na Y osi vrednosti. To znaci da nama treba ovakva tabela:
Code:
Field Value
----------------------
Potroseno 1234.56
Zaradjeno 43.21
Porez 77.5
pa u Birt-u mozemo da stavimo da je na X osi [Field] a na Y osi [Value].
Evo koda za funkciju fn_row_to_column koju sam uspeo da napravim zahvaljujuci tome sto sam na onom sajtu video kako da saznam imena kolona u dinamickom queryiju.
Code:
DROP TYPE field_value_table_type;
CREATE OR REPLACE TYPE field_value_row_type AS OBJECT (
Field VARCHAR2(30),
Value VARCHAR2(30)
);
CREATE OR REPLACE TYPE field_value_table_type AS TABLE OF field_value_row_type;
CREATE OR REPLACE FUNCTION fn_row_to_column (
p_query VARCHAR2)
RETURN field_value_table_type PIPELINED AS
col_name_temp varchar2(4000); --current column name
value_temp NUMBER; --current value
l_statement1 VARCHAR2 (4000);
l_statement2 VARCHAR2 (4000);
l_sql_whole VARCHAR2(4000);
l_cursor PLS_INTEGER := DBMS_SQL.open_cursor;
cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_columns DBMS_SQL.desc_tab2;
l_numcols PLS_INTEGER;
l_field VARCHAR(30);
l_value VARCHAR2 (30);
l_feedback PLS_INTEGER;
indx PLS_INTEGER:=1;
BEGIN
-- Parse the query.
DBMS_SQL.parse ( l_cursor, p_query, DBMS_SQL.native );
-- Retrieve column information
DBMS_SQL.describe_columns2 ( l_cursor, l_numcols, l_columns );
-- Define each of the column names
FOR colind IN 1 .. l_numcols
LOOP
-- Specify maximum size of the string being retrieved.
DBMS_SQL.define_column ( l_cursor, colind, l_value, 30 );
END LOOP;
-- Now execute the query....
l_feedback := DBMS_SQL.EXECUTE ( l_cursor );
l_statement1:='DECODE(rownum';
l_statement2:='DECODE(rownum';
LOOP
EXIT WHEN DBMS_SQL.fetch_rows ( l_cursor ) = 0;
FOR colind IN 1 .. l_numcols
LOOP
-- Retrieve each value.
DBMS_SQL.COLUMN_VALUE ( l_cursor, colind, l_value );
l_statement1:=l_statement1 || ',' || indx || ',' || '''' || l_columns(colind).col_name || '''';
l_statement2:=l_statement2 || ',' || indx || ',' || l_value;
indx:=indx+1;
END LOOP;
END LOOP;
l_sql_whole:='SELECT ' || l_statement1 || ') AS Field,' || l_statement2 || ') AS Value FROM (' || p_query || ') CONNECT BY ROWNUM <=' || l_numcols;
-- Cleanup
DBMS_SQL.close_cursor ( l_cursor );
DBMS_SQL.parse ( cur, l_sql_whole, DBMS_SQL.native );
FOR colind IN 1 .. 2
LOOP
DBMS_SQL.define_column ( cur, colind, l_value, 30 );
END LOOP;
l_feedback := DBMS_SQL.EXECUTE ( cur );
LOOP
EXIT WHEN DBMS_SQL.fetch_rows ( cur ) = 0;
DBMS_SQL.COLUMN_VALUE ( cur, 1, l_field );
DBMS_SQL.COLUMN_VALUE ( cur, 2, l_value );
PIPE ROW(field_value_row_type(l_field, l_value));
END LOOP;
RETURN;
END;
Startujte ovu gore skriptu i onda mozete to da koristite u Birt ili bilo gde drugde na ovakav nacin:
Code:
SELECT * FROM TABLE(fn_row_to_column('SELECT sum(purchase) as Potroseno, sum (depozit) as Zaradjeno, sum (taksa) as Porez FROM Transakcije'))
i to nam vrati onu tabelu koju smo hteli. Ako nekome nije jasno kako ovaj moj kod radi mogu kasnije da ga pojasnim.
ispisivanje kolone bez poznavanja imena kolone
