how call function from ireport

[pcouas]

Hi

I search how calling following PLSQL function from IREPORT

function F_GET_GRAPH_BY_VESSEL4return VARCHAR2isl_qry VARCHAR2(9767);BEGINl_qry := 'SELECT  label, ';FOR r1 IN (SELECT DISTINCT VESSEL_UID FROM lo_valuation order by VESSEL_UID)  LOOPl_qry := l_qry || 'sum(decode(VESSEL_UID,' || r1.VESSEL_UID || ',value1,0)) N'||r1.VESSEL_uid||',';  END LOOP;l_qry := rtrim(l_qry, ',');  --Append the rest of the query  l_qry := l_qry || ' FROM  (SELECT TO_CHAR(modif_date,''YYYYMM'') ,    TO_CHAR(modif_date,''MON-YYYY'') label,    NVL(SUM(QUANTITY_DELIV),0) value1, VESSEL_UID  FROM "SCE_PROD"."LO_VALUATION"  GROUP BY TO_CHAR(modif_date,''YYYYMM''),    TO_CHAR(modif_date,''MON-YYYY''), VESSEL_UID  ORDER BY TO_CHAR(modif_date,''YYYYMM''),    TO_CHAR(modif_date,''MON-YYYY''), VESSEL_UID) GROUP BY label order by label';  RETURN l_qry;END;       [/code]

 

Give me result

 

SELECT  label, sum(decode(VESSEL_UID,1,value1,0)) N1 ,sum(decode(VESSEL_UID,2,value1,0)) N2 ,sum(decode(VESSEL_UID,3,value1,0)) N3 ,sum(decode(VESSEL_UID,4,value1,0)) N4 ,sum(decode(VESSEL_UID,5,value1,0)) N5 ,sum(decode(VESSEL_UID,6,value1,0)) N6 ,sum(decode(VESSEL_UID,7,value1,0)) N7 ,sum(decode(VESSEL_UID,8,value1,0)) N8 ,sum(decode(VESSEL_UID,9,value1,0)) N9 ,sum(decode(VESSEL_UID,10,value1,0)) N10 ,sum(decode(VESSEL_UID,11,value1,0)) N11 ,sum(decode(VESSEL_UID,12,value1,0)) N12 ,sum(decode(VESSEL_UID,13,value1,0)) N13 ,sum(decode(VESSEL_UID,14,value1,0)) N14 ,sum(decode(VESSEL_UID,15,value1,0)) N15 ,sum(decode(VESSEL_UID,16,value1,0)) N16 ,sum(decode(VESSEL_UID,17,value1,0)) N17 ,sum(decode(VESSEL_UID,18,value1,0)) N18 ,sum(decode(VESSEL_UID,19,value1,0)) N19 ,sum(decode(VESSEL_UID,20,value1,0)) N20 ,sum(decode(VESSEL_UID,21,value1,0)) N21 ,sum(decode(VESSEL_UID,22,value1,0)) N22 ,sum(decode(VESSEL_UID,23,value1,0)) N23 ,sum(decode(VESSEL_UID,24,value1,0)) N24 ,sum(decode(VESSEL_UID,25,value1,0)) N25 ,sum(decode(VESSEL_UID,26,value1,0)) N26 ,sum(decode(VESSEL_UID,27,value1,0)) N27 ,sum(decode(VESSEL_UID,28,value1,0)) N28 ,sum(decode(VESSEL_UID,29,value1,0)) N29 ,sum(decode(VESSEL_UID,30,value1,0)) N30 ,sum(decode(VESSEL_UID,31,value1,0)) N31 ,sum(decode(VESSEL_UID,32,value1,0)) N32 ,sum(decode(VESSEL_UID,33,value1,0)) N33 ,sum(decode(VESSEL_UID,34,value1,0)) N34 ,sum(decode(VESSEL_UID,35,value1,0)) N35 ,sum(decode(VESSEL_UID,36,value1,0)) N36 ,sum(decode(VESSEL_UID,37,value1,0)) N37 ,sum(decode(VESSEL_UID,38,value1,0)) N38 ,sum(decode(VESSEL_UID,39,value1,0)) N39 ,sum(decode(VESSEL_UID,40,value1,0)) N40 ,sum(decode(VESSEL_UID,41,value1,0)) N41 ,sum(decode(VESSEL_UID,43,value1,0)) N43 ,sum(decode(VESSEL_UID,44,value1,0)) N44 ,sum(decode(VESSEL_UID,45,value1,0)) N45 ,sum(decode(VESSEL_UID,46,value1,0)) N46 ,sum(decode(VESSEL_UID,47,value1,0)) N47 ,sum(decode(VESSEL_UID,48,value1,0)) N48 ,sum(decode(VESSEL_UID,49,value1,0)) N49 ,sum(decode(VESSEL_UID,50,value1,0)) N50 ,sum(decode(VESSEL_UID,51,value1,0)) N51 ,sum(decode(VESSEL_UID,53,value1,0)) N53 ,sum(decode(VESSEL_UID,54,value1,0)) N54 ,sum(decode(VESSEL_UID,55,value1,0)) N55 ,sum(decode(VESSEL_UID,56,value1,0)) N56 ,sum(decode(VESSEL_UID,57,value1,0)) N57 ,sum(decode(VESSEL_UID,58,value1,0)) N58 ,sum(decode(VESSEL_UID,59,value1,0)) N59 ,sum(decode(VESSEL_UID,60,value1,0)) N60 ,sum(decode(VESSEL_UID,61,value1,0)) N61 ,sum(decode(VESSEL_UID,62,value1,0)) N62 ,sum(decode(VESSEL_UID,64,value1,0)) N64 ,sum(decode(VESSEL_UID,65,value1,0)) N65 ,sum(decode(VESSEL_UID,66,value1,0)) N66 ,sum(decode(VESSEL_UID,67,value1,0)) N67 ,sum(decode(VESSEL_UID,68,value1,0)) N68 ,sum(decode(VESSEL_UID,69,value1,0)) N69 ,sum(decode(VESSEL_UID,70,value1,0)) N70 ,sum(decode(VESSEL_UID,71,value1,0)) N71 ,sum(decode(VESSEL_UID,72,value1,0)) N72 ,sum(decode(VESSEL_UID,73,value1,0)) N73 ,sum(decode(VESSEL_UID,74,value1,0)) N74 ,sum(decode(VESSEL_UID,75,value1,0)) N75 ,sum(decode(VESSEL_UID,76,value1,0)) N76 ,sum(decode(VESSEL_UID,77,value1,0)) N77 ,sum(decode(VESSEL_UID,78,value1,0)) N78 ,sum(decode(VESSEL_UID,79,value1,0)) N79 ,sum(decode(VESSEL_UID,80,value1,0)) N80 ,sum(decode(VESSEL_UID,81,value1,0)) N81 ,sum(decode(VESSEL_UID,82,value1,0)) N82 ,sum(decode(VESSEL_UID,83,value1,0)) N83 ,sum(decode(VESSEL_UID,84,value1,0)) N84 ,sum(decode(VESSEL_UID,85,value1,0)) N85 ,sum(decode(VESSEL_UID,86,value1,0)) N86 ,sum(decode(VESSEL_UID,87,value1,0)) N87 ,sum(decode(VESSEL_UID,88,value1,0)) N88 ,sum(decode(VESSEL_UID,89,value1,0)) N89 ,sum(decode(VESSEL_UID,90,value1,0)) N90 ,sum(decode(VESSEL_UID,91,value1,0)) N91 ,sum(decode(VESSEL_UID,92,value1,0)) N92 ,sum(decode(VESSEL_UID,93,value1,0)) N93 ,sum(decode(VESSEL_UID,94,value1,0)) N94 ,sum(decode(VESSEL_UID,95,value1,0)) N95 ,sum(decode(VESSEL_UID,96,value1,0)) N96 ,sum(decode(VESSEL_UID,97,value1,0)) N97 ,sum(decode(VESSEL_UID,98,value1,0)) N98 ,sum(decode(VESSEL_UID,102,value1,0)) N102 ,sum(decode(VESSEL_UID,103,value1,0)) N103 ,sum(decode(VESSEL_UID,104,value1,0)) N104 ,sum(decode(VESSEL_UID,106,value1,0)) N106 ,sum(decode(VESSEL_UID,107,value1,0)) N107 ,sum(decode(VESSEL_UID,108,value1,0)) N108 ,sum(decode(VESSEL_UID,109,value1,0)) N109 ,sum(decode(VESSEL_UID,110,value1,0)) N110 ,sum(decode(VESSEL_UID,111,value1,0)) N111 ,sum(decode(VESSEL_UID,112,value1,0)) N112 ,sum(decode(VESSEL_UID,113,value1,0)) N113 ,sum(decode(VESSEL_UID,114,value1,0)) N114 ,sum(decode(VESSEL_UID,115,value1,0)) N115 ,sum(decode(VESSEL_UID,116,value1,0)) N116 ,sum(decode(VESSEL_UID,117,value1,0)) N117 ,sum(decode(VESSEL_UID,118,value1,0)) N118 ,sum(decode(VESSEL_UID,119,value1,0)) N119 ,sum(decode(VESSEL_UID,120,value1,0)) N120 ,sum(decode(VESSEL_UID,121,value1,0)) N121 ,sum(decode(VESSEL_UID,122,value1,0)) N122 ,sum(decode(VESSEL_UID,124,value1,0)) N124 ,sum(decode(VESSEL_UID,132,value1,0)) N132 ,sum(decode(VESSEL_UID,134,value1,0)) N134 ,sum(decode(VESSEL_UID,135,value1,0)) N135 ,sum(decode(VESSEL_UID,141,value1,0)) N141 ,sum(decode(VESSEL_UID,142,value1,0)) N142 ,sum(decode(VESSEL_UID,143,value1,0)) N143 ,sum(decode(VESSEL_UID,144,value1,0)) N144 ,sum(decode(VESSEL_UID,145,value1,0)) N145 ,sum(decode(VESSEL_UID,147,value1,0)) N147 ,sum(decode(VESSEL_UID,148,value1,0)) N148 ,sum(decode(VESSEL_UID,149,value1,0)) N149 ,sum(decode(VESSEL_UID,150,value1,0)) N150 ,sum(decode(VESSEL_UID,152,value1,0)) N152 ,sum(decode(VESSEL_UID,154,value1,0)) N154 ,sum(decode(VESSEL_UID,155,value1,0)) N155 ,sum(decode(VESSEL_UID,158,value1,0)) N158 ,sum(decode(VESSEL_UID,167,value1,0)) N167 ,sum(decode(VESSEL_UID,168,value1,0)) N168  FROM
  (SELECT TO_CHAR(modif_date,'YYYYMM') ,
    TO_CHAR(modif_date,'MON-YYYY') label,
    NVL(SUM(QUANTITY_DELIV),0) value1, VESSEL_UID
  FROM "SCE_PROD"."LO_VALUATION"
  GROUP BY TO_CHAR(modif_date,'YYYYMM'),
    TO_CHAR(modif_date,'MON-YYYY'), VESSEL_UID
  ORDER BY TO_CHAR(modif_date,'YYYYMM'),
    TO_CHAR(modif_date,'MON-YYYY'), VESSEL_UID
) GROUP BY label order by label
 

How could calling from IREPORT

I have tried without succes SQL select DP_LO_GRAPH.F_GET_GRAPH_BY_VESSEL4 from dual

AND PLSQL {CALL DP_LO_GRAPH.F_GET_GRAPH_BY_VESSEL4()}

 

Regards

Phil