beforeOpen in pivot operation.
<p>Hello,</p><p> </p><p>I'm trying to chose what column to SUM in a PIVOT using the beforeOpen operation to replace the value with a parameter. However, I get an exception when I try to validate the Data Set:</p><p> </p><p> </p><div>[font="'courier new', courier, monospace;"]A BIRT exception occurred.[/font]</div><div>[font="'courier new', courier, monospace;"] Plug-in Provider:Eclipse BIRT Project[/font]</div><div>[font="'courier new', courier, monospace;"] Plug-in Name:BIRT Data Engine[/font]</div><div>[font="'courier new', courier, monospace;"] Plug-in ID:org.eclipse.birt.data[/font]</div><div>[font="'courier new', courier, monospace;"] Version:3.7.2.v20120213[/font]</div><div>[font="'courier new', courier, monospace;"] Error Code:odaconsumer.CannotGetResultSetMetaData[/font]</div><div>[font="'courier new', courier, monospace;"] Error Message:Cannot get the result set metadata.[/font]</div><div>[font="'courier new', courier, monospace;"] org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.[/font]</div><div>[font="'courier new', courier, monospace;"]SQL error #1:ORA-00918: column ambiguously defined[/font]</div><div> </div><div>[font="'courier new', courier, monospace;"] ;[/font]</div><div>[font="'courier new', courier, monospace;"] java.sql.SQLSyntaxErrorException:[color=#ff0000;] ORA-00918: column ambiguously defined[/color][/font]</div><div> </div><div> </div><div> </div><div>How is is possible to do something like this:</div><div> </div><div> </div><div><div>[font="'courier new', courier, monospace;"]select y.*[/font]</div><div>[font="'courier new', courier, monospace;"]FROM ([/font]</div><div>[font="'courier new', courier, monospace;"] select to_char( TABLE.STARTTIME, 'dd' ) AS DAY [/font]</div><div>[font="'courier new', courier, monospace;"] NULLIF( ROUND(to_number( TABLE.SUM/TABLE.COUNT ),2),0) AGG,[/font]</div><div>[font="'courier new', courier, monospace;"] NULLIF( to_number( TABLE.COUNT ),0) COUNT,[/font]</div><div>[font="'courier new', courier, monospace;"] NULLIF( to_number( TABLE.SUM ),0) SUM [/font]</div><div>[font="'courier new', courier, monospace;"]from TABLE[/font]</div><div>[font="'courier new', courier, monospace;"]WHERE COLUMN2 IN ('column2_report_param')[/font]</div><div>[font="'courier new', courier, monospace;"])[/font]</div><div>[font="'courier new', courier, monospace;"]pivot ([/font]</div><div>[font="'courier new', courier, monospace;"] SUM('calculation_report_param') for day in[/font]</div><div>[font="'courier new', courier, monospace;"] ('01','02','03','04','05','06','07','08','09','10','11','12', '13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31')[/font]</div><div>[font="'courier new', courier, monospace;"]) y[/font]</div><div> </div></div><div> </div><div> </div><div>It works if I hardcode </div><div>[font="'courier new', courier, monospace;"][color=#ff0000;] SUM('calculation_report_param') [/color][/font]</div><div>to </div><div>[color=#ff8c00;][font="'courier new', courier, monospace;"] SUM(AGG) or SUM(COUNT) or SUM (SUM)[/color][/font]</div><div> </div><div>Thanks all,</div><div> </div>
